package com.credit.controller;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.credit.mapper.administrativeMapper;
import com.credit.mapper.analysisChartMapper;
import com.credit.mapper.balanceSheetMapper;
import com.credit.mapper.branchMapper;
import com.credit.mapper.brandMapper;
import com.credit.mapper.companyMapper;
import com.credit.mapper.copyrightMapper;
import com.credit.mapper.creditLoanMapper;
import com.credit.mapper.debtRiskMapper;
import com.credit.mapper.developmentMapper;
import com.credit.mapper.economicGainMapper;
import com.credit.mapper.enterpriseHonorMapper;
import com.credit.mapper.enterpriseInformationMapper;
import com.credit.mapper.environmentalMapper;
import com.credit.mapper.flowsMapper;
import com.credit.mapper.frameworkMapper;
import com.credit.mapper.historicMapper;
import com.credit.mapper.industryMapper;
import com.credit.mapper.jobMapper;
import com.credit.mapper.judicialMapper;
import com.credit.mapper.managementSystemMapper;
import com.credit.mapper.occupationalHealthMapper;
import com.credit.mapper.otherProductsMapper;
import com.credit.mapper.patentMapper;
import com.credit.mapper.patentTypeMapper;
import com.credit.mapper.professionalWorkMapper;
import com.credit.mapper.profitMapper;
import com.credit.mapper.profitabilityMapper;
import com.credit.mapper.publicWelfareMapper;
import com.credit.mapper.qualificationMapper;
import com.credit.mapper.qualityAssetsMapper;
import com.credit.mapper.qualityControlMapper;
import com.credit.mapper.seniorExecutiveMapper;
import com.credit.mapper.shareholderMapper;
import com.credit.mapper.socialContributionMapper;
import com.credit.mapper.violationOperationMapper;
import com.credit.pojo.administrative;
import com.credit.pojo.analysisChart;
import com.credit.pojo.balanceSheet;
import com.credit.pojo.branch;
import com.credit.pojo.brand;
import com.credit.pojo.company;
import com.credit.pojo.copyright;
import com.credit.pojo.creditLoan;
import com.credit.pojo.debtRisk;
import com.credit.pojo.development;
import com.credit.pojo.economicGain;
import com.credit.pojo.enterpriseHonor;
import com.credit.pojo.enterpriseInformation;
import com.credit.pojo.environmental;
import com.credit.pojo.flows;
import com.credit.pojo.framework;
import com.credit.pojo.historic;
import com.credit.pojo.industry;
import com.credit.pojo.job;
import com.credit.pojo.judicial;
import com.credit.pojo.managementSystem;
import com.credit.pojo.occupationalHealth;
import com.credit.pojo.otherProducts;
import com.credit.pojo.patent;
import com.credit.pojo.patentType;
import com.credit.pojo.professionalWork;
import com.credit.pojo.profit;
import com.credit.pojo.profitability;
import com.credit.pojo.publicWelfare;
import com.credit.pojo.qualification;
import com.credit.pojo.qualityAssets;
import com.credit.pojo.qualityControl;
import com.credit.pojo.seniorExecutive;
import com.credit.pojo.shareholder;
import com.credit.pojo.socialContribution;
import com.credit.pojo.violationOperation;
import com.credit.vo.RateVo;
import com.zhuozhengsoft.pageoffice.OpenModeType;
import com.zhuozhengsoft.pageoffice.PageOfficeCtrl;
import com.zhuozhengsoft.pageoffice.wordwriter.DataRegion;
import com.zhuozhengsoft.pageoffice.wordwriter.Table;
import com.zhuozhengsoft.pageoffice.wordwriter.WordDocument;


@Controller
public class PageOfficeController {
	
	@Autowired
	private companyMapper companyMapper;
	
	@Autowired
	private enterpriseInformationMapper enterpriseInformationMapper;
	
	@Autowired
	private balanceSheetMapper balanceSheetMapper;
	
	@Autowired
	private flowsMapper flowsMapper;
	
	@Autowired
	private profitMapper profitMapper;

	@Autowired
	private shareholderMapper shareholderMapper;
	
	@Autowired
	private historicMapper historicMapper;
	
	@Autowired
	private branchMapper branchMapper;
	
	@Autowired
	private frameworkMapper frameworkMapper;
	
	@Autowired
	private managementSystemMapper managementSystemMapper;
	
	@Autowired
	private seniorExecutiveMapper seniorExecutiveMapper;
	
	@Autowired
	private jobMapper jobMapper;
	
	@Autowired
	private brandMapper brandMapper;
	
	@Autowired
	private patentTypeMapper patentTypeMapper;
	
	@Autowired
	private patentMapper patentMapper; 
	
	@Autowired
	private copyrightMapper copyrightMapper;
	
	@Autowired
	private administrativeMapper administrativeMapper;
	
	@Autowired
	private judicialMapper judicialMapper;
	
	@Autowired
	private violationOperationMapper violationOperationMapper;
	
	@Autowired
	private creditLoanMapper creditLoanMapper;
	
	@Autowired
	private qualityControlMapper qualityControlMapper;
	
	@Autowired
	private environmentalMapper environmentalMapper;
	
	@Autowired
	private occupationalHealthMapper occupationalHealthMapper;
	
	@Autowired
	private otherProductsMapper otherProductsMapper;
	
	@Autowired
	private qualificationMapper qualificationMapper;
	
	@Autowired
	private professionalWorkMapper professionalWorkMapper;
	
	@Autowired
	private economicGainMapper economicGainMapper;
	
	@Autowired
	private analysisChartMapper analysisChartMapper;
	
	@Autowired
	private debtRiskMapper debtRiskMapper;
	
	@Autowired
	private qualityAssetsMapper qualityAssetsMapper;
	
	@Autowired
	private profitabilityMapper profitabilityMapper;
	
	@Autowired
	private socialContributionMapper socialContributionMapper; 
	
	@Autowired
	private publicWelfareMapper publicWelfareMapper;
	
	@Autowired
	private enterpriseHonorMapper enterpriseHonorMapper;
	
	@Autowired
	private industryMapper industryMapper;
	
	@Autowired
	private developmentMapper developmentMapper;
	
	public static String formatDateTime(Date date,String dft){
		String rslt="";
        SimpleDateFormat sdf = new SimpleDateFormat();
        if(dft.equals("short")){
        	dft="yyyy年 MM月 dd日";
        }else if(dft.equals("long")){
        	dft="yyyy-MM-dd HH:mm:ss";	        	
        }else if(dft.equals("HH:mm")){
        	dft="HH:mm";
        }else if(dft.equals("QRlong")){
        	dft="yyyyMMdd";
        }
        sdf.applyPattern(dft);
        rslt=sdf.format(date);
		return rslt;
	}
	
	
	@RequestMapping("openword111")
	public String openword111(HttpServletRequest request,Integer id ) {
		PageOfficeCtrl poCtrl=new PageOfficeCtrl(request);
		request.setAttribute("poCtrl", poCtrl);
		//设置服务器页面
		poCtrl.setServerPage(request.getContextPath()+"/poserver.zz");
		WordDocument doc = new WordDocument();
		//获取企业信息
		company company=companyMapper.selectByPrimaryKey(id); 
		boolean bool = true;
		String ent_nama=company.getName();
		//打开数据区域
		DataRegion dataRegion1 = doc.openDataRegion("PO_ENTERPRISE_NAME");
		dataRegion1.setValue(ent_nama);
		
		DataRegion dataRegion2 = doc.openDataRegion("PO_ENTERPRISE_TIME");
		DataRegion dataRegion172 = doc.openDataRegion("PO_ENTERPRISE_TIME1");
		DataRegion dataRegion173 = doc.openDataRegion("PO_ENTERPRISE_TIME2");
		dataRegion2.setValue(formatDateTime(new Date(), "short"));
		dataRegion172.setValue(formatDateTime(new Date(), "short"));
		dataRegion173.setValue(formatDateTime(new Date(), "short"));
		
		DataRegion dataRegion3 = doc.openDataRegion("PO_ENTERPRISE_NAME1");
		dataRegion3.setValue(ent_nama);
		DataRegion dataRegion5 = doc.openDataRegion("PO_ENTERPRISE_NAME2");
		dataRegion5.setValue(ent_nama);
		DataRegion dataRegion174 = doc.openDataRegion("PO_NUMBER");
		dataRegion174.setValue(formatDateTime(new Date(), "QRlong")+id);
		//总结
		DataRegion dataRegion146 = doc.openDataRegion("PO_FINAL_REPORT");
		DataRegion dataRegion147 = doc.openDataRegion("PO_FINAL_REPORT1");
		DataRegion dataRegion148 = doc.openDataRegion("PO_FINAL_REPORT2");
		DataRegion dataRegion149 = doc.openDataRegion("PO_FINAL_REPORT3");
		DataRegion dataRegion150 = doc.openDataRegion("PO_FINAL_REPORT4");
		DataRegion dataRegion151 = doc.openDataRegion("PO_FINAL_REPORT5");

		
		
		//获取企业基本信息
		enterpriseInformation enterpriseInformation = enterpriseInformationMapper.selectEnterpriseInformation(id);
		
		DataRegion dataRegion4 = doc.openDataRegion("PO_ESSENTIAL_INFORMATION");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table = dataRegion4.openTable(1);
		
		//给table中的单元格赋值， openCellRC(int,int)中的参数分别代表第几行、第几列，从1开始
		table.openCellRC(1, 2).setValue(enterpriseInformation.getName());
		table.openCellRC(2, 2).setValue(enterpriseInformation.getAddress());
		table.openCellRC(3, 2).setValue(enterpriseInformation.getLegalperson());
		table.openCellRC(4, 2).setValue(enterpriseInformation.getRegisteredcapital());
		table.openCellRC(5, 2).setValue(enterpriseInformation.getEconomictype());
		table.openCellRC(6, 2).setValue(enterpriseInformation.getEstablishmenttime());
		table.openCellRC(7, 2).setValue(enterpriseInformation.getUnifiedsocialcreditcode());
		
		//信用等级
		industry industry = industryMapper.selectByIndustry(enterpriseInformation.getIndustryid());
		DataRegion dataRegion57 = doc.openDataRegion("PO_LEVEL");
		Table table34 = dataRegion57.openTable(1);
		DataRegion dataRegion163 = doc.openDataRegion("PO_LAST9");
		Table table35 = dataRegion163.openTable(1);
		DataRegion dataRegion180 = doc.openDataRegion("PO_LEVEL1");
		if(company.getScore() >= 90) {
			table34.openCellRC(1, 2).setValue("AAA");
			table34.openCellRC(2, 2).setValue("企业信用程度极好");
			
			table35.openCellRC(2, 1).setValue("AAA");
			table35.openCellRC(2, 2).setValue("≥90");
			table35.openCellRC(2, 3).setValue("程度极好");
			table35.openCellRC(2, 4).setValue("企业信用程度极好");
			
			dataRegion180.setValue("企业信用程度极好");
		}else if(90 < company.getScore() || company.getScore()>=80) {
			table34.openCellRC(1, 2).setValue("AA");
			table34.openCellRC(2, 2).setValue("企业信用程度良好");
			
			table35.openCellRC(2, 1).setValue("AA");
			table35.openCellRC(2, 2).setValue("≥80＜90");
			table35.openCellRC(2, 3).setValue("程度良好");
			table35.openCellRC(2, 4).setValue("企业信用程度良好");
			
			dataRegion180.setValue("企业信用程度良好");
		}else if(80 < company.getScore() || company.getScore()>=70) {
			table34.openCellRC(1, 2).setValue("A");
			table34.openCellRC(2, 2).setValue("企业信用程度较好");
			
			table35.openCellRC(2, 1).setValue("A");
			table35.openCellRC(2, 2).setValue("≥70＜80");
			table35.openCellRC(2, 3).setValue("程度较好");
			table35.openCellRC(2, 4).setValue("企业信用程度较好");
			
			dataRegion180.setValue("企业信用程度较好");
		}else if(70 < company.getScore() || company.getScore()>=60) {
			table34.openCellRC(1, 2).setValue("BBB");
			table34.openCellRC(2, 2).setValue("企业信用程度一般");
			
			table35.openCellRC(2, 1).setValue("BBB");
			table35.openCellRC(2, 2).setValue("≥60＜70");
			table35.openCellRC(2, 3).setValue("程度一般");
			table35.openCellRC(2, 4).setValue("企业信用程度一般");
			
			dataRegion180.setValue("企业信用程度一般");
		}else if(60 < company.getScore() || company.getScore()>=50) {
			table34.openCellRC(1, 2).setValue("BB");
			table34.openCellRC(2, 2).setValue("企业信用程度较差");
			
			table35.openCellRC(2, 1).setValue("BB");
			table35.openCellRC(2, 2).setValue("≥50＜60");
			table35.openCellRC(2, 3).setValue("程度较差");
			table35.openCellRC(2, 4).setValue("企业信用程度较差");
			
			dataRegion180.setValue("企业信用程度较差");
		}else if(50 < company.getScore() || company.getScore()>=40) {
			table34.openCellRC(1, 2).setValue("B");
			table34.openCellRC(2, 2).setValue("企业信用程度差");
			
			table35.openCellRC(2, 1).setValue("B");
			table35.openCellRC(2, 2).setValue("≥40＜50");
			table35.openCellRC(2, 3).setValue("程度差");
			table35.openCellRC(2, 4).setValue("企业信用程度差");
			
			dataRegion180.setValue("企业信用程度差");
		}else if(40 < company.getScore() || company.getScore()>=30) {
			table34.openCellRC(1, 2).setValue("CCC");
			table34.openCellRC(2, 2).setValue("企业信用程度很差");
			
			table35.openCellRC(2, 1).setValue("CCC");
			table35.openCellRC(2, 2).setValue("≥30＜40");
			table35.openCellRC(2, 3).setValue("程度很差");
			table35.openCellRC(2, 4).setValue("企业信用程度很差");
			
			dataRegion180.setValue("企业信用程度很差");
		}else if(30 < company.getScore() || company.getScore()>=20) {
			table34.openCellRC(1, 2).setValue("CC");
			table34.openCellRC(2, 2).setValue("企业信用程度极差");
			
			table35.openCellRC(2, 1).setValue("CC");
			table35.openCellRC(2, 2).setValue("≥20＜30");
			table35.openCellRC(2, 3).setValue("程度极差");
			table35.openCellRC(2, 4).setValue("企业信用程度极差");
			
			dataRegion180.setValue("企业信用程度极差");
		}else if(20 < company.getScore() || company.getScore()>=10) {
			table34.openCellRC(1, 2).setValue("C");
			table34.openCellRC(2, 2).setValue("企业无信用");
			
			table35.openCellRC(2, 1).setValue("C");
			table35.openCellRC(2, 2).setValue("≥10＜20");
			table35.openCellRC(2, 3).setValue("无信用");
			table35.openCellRC(2, 4).setValue("企业无信用");
			
			dataRegion180.setValue("企业无信用");
		}else {
			table34.openCellRC(1, 2).setValue("D");
			table34.openCellRC(2, 2).setValue("企业已濒临破产");
			
			table35.openCellRC(2, 1).setValue("D");
			table35.openCellRC(2, 2).setValue("＜10");
			table35.openCellRC(2, 3).setValue("濒临破产");
			table35.openCellRC(2, 4).setValue("企业已濒临破产");
			
			dataRegion180.setValue("企业已濒临破产");
		}
		
		table34.openCellRC(3, 2).setValue(industry.getName());
		DataRegion dataRegion164 = doc.openDataRegion("PO_LAST10");
		String type = "";
		if(enterpriseInformation.getScaleid() == 0) {
			type = "全部类型";
		}else if(enterpriseInformation.getScaleid() == 1) {
			type = "大型企业";
		}else if(enterpriseInformation.getScaleid() == 1) {
			type = "中型企业";
		}else {
			type = "小型企业";
		}
		dataRegion164.setValue(industry.getName()+"————"+type);
		
		
		DataRegion dataRegion6 = doc.openDataRegion("PO_ESSENTIAL_INFORMATION1");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table1 = dataRegion6.openTable(1);
		
		//给table中的单元格赋值， openCellRC(int,int)中的参数分别代表第几行、第几列，从1开始
		table1.openCellRC(1, 2).setValue(enterpriseInformation.getName());
		table1.openCellRC(2, 2).setValue(enterpriseInformation.getLegalperson());
		table1.openCellRC(2, 4).setValue(enterpriseInformation.getPhone());
		table1.openCellRC(3, 2).setValue(enterpriseInformation.getAddress());
		table1.openCellRC(3, 4).setValue(enterpriseInformation.getDawk().toString());
		table1.openCellRC(4, 2).setValue(enterpriseInformation.getEconomictype());
		table1.openCellRC(4, 4).setValue(enterpriseInformation.getEstablishmenttime());
		if(enterpriseInformation.getScience() == 0){
			table1.openCellRC(5, 2).setValue("否");
		}else {
			table1.openCellRC(5, 2).setValue("是");
		}
		table1.openCellRC(5, 4).setValue(enterpriseInformation.getUnifiedsocialcreditcode());
		table1.openCellRC(6, 2).setValue(enterpriseInformation.getRegisteredcapital());
		table1.openCellRC(6, 4).setValue(enterpriseInformation.getOpeningbank());
		table1.openCellRC(7, 2).setValue(enterpriseInformation.getCreditcardnumber());
		table1.openCellRC(7, 4).setValue(enterpriseInformation.getAccount());
		table1.openCellRC(8, 2).setValue(enterpriseInformation.getBusinessscope()); 
		
		
		
		
		
		
		//底部总结
		DataRegion dataRegion154 = doc.openDataRegion("PO_LAST");
		DataRegion dataRegion155 = doc.openDataRegion("PO_LAST1");
		DataRegion dataRegion156 = doc.openDataRegion("PO_LAST2");
		DataRegion dataRegion157 = doc.openDataRegion("PO_LAST3");
		DataRegion dataRegion158 = doc.openDataRegion("PO_LAST4");
		DataRegion dataRegion159 = doc.openDataRegion("PO_LAST5"); 
		DataRegion dataRegion160 = doc.openDataRegion("PO_LAST6");
		DataRegion dataRegion161 = doc.openDataRegion("PO_LAST7");
		DataRegion dataRegion162 = doc.openDataRegion("PO_LAST8");
		
		//资产负债表
		DataRegion dataRegion7 = doc.openDataRegion("PO_BALANCESHEET");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table2 = dataRegion7.openTable(1);
		List<balanceSheet> balanceSheetList = balanceSheetMapper.selectBalanceSheetKey(id);
		
		for(int i = 0; i<balanceSheetList.size();i++) {
			//获取最远的一个年份
			balanceSheet balancesheet = balanceSheetList.get(i);    
			//获取数据所在的列数
			int series = i+3;
			table2.openCellRC(1, series).setValue(balancesheet.getYear().toString());
			//table2.openCellRC(2, series).setValue(balancesheet.getCurrentassets().toString());
			table2.openCellRC(3, series).setValue(balancesheet.getMonetarycapital().toString());
			table2.openCellRC(4, series).setValue(balancesheet.getTradingfinancialassets().toString());
			table2.openCellRC(5, series).setValue(balancesheet.getNotesreceivable().toString());
			table2.openCellRC(6, series).setValue(balancesheet.getReceivables().toString());
			table2.openCellRC(7, series).setValue(balancesheet.getPrepayment().toString());
			table2.openCellRC(8, series).setValue(balancesheet.getInterestreceivable().toString());
			table2.openCellRC(9, series).setValue(balancesheet.getDividendsreceivable().toString());
			table2.openCellRC(10, series).setValue(balancesheet.getOtherreceivables().toString());
			table2.openCellRC(11, series).setValue(balancesheet.getInventory().toString());
			table2.openCellRC(12, series).setValue(balancesheet.getIlliquidassets().toString());
			table2.openCellRC(13, series).setValue(balancesheet.getCurrentassets().toString());
			table2.openCellRC(14, series).setValue(balancesheet.getTotalcurrentassets().toString());
			//table2.openCellRC(15, series).setValue(balancesheet.getYear().toString());
			table2.openCellRC(16, series).setValue(balancesheet.getSaleoffinancialassets().toString());
			table2.openCellRC(17, series).setValue(balancesheet.getHeldToMaturityiivestment().toString());
			table2.openCellRC(18, series).setValue(balancesheet.getLongTermreceivables().toString());
			table2.openCellRC(19, series).setValue(balancesheet.getLongTermequityinvestment().toString());
			table2.openCellRC(20, series).setValue(balancesheet.getInvestmentproperty().toString());
			//table2.openCellRC(21, series).setValue(balancesheet.getYear().toString());
			table2.openCellRC(22, series).setValue(balancesheet.getImmobilisations().toString());
			table2.openCellRC(23, series).setValue(balancesheet.getAccumulateddepreciation().toString());
			table2.openCellRC(24, series).setValue(balancesheet.getNetvalueoffixedassets().toString());
			table2.openCellRC(25, series).setValue(balancesheet.getFixedassetsdepreciationreserves().toString());
			table2.openCellRC(26, series).setValue(balancesheet.getNetfixedassets().toString());
			table2.openCellRC(27, series).setValue(balancesheet.getConstructioninprocess().toString());
			table2.openCellRC(28, series).setValue(balancesheet.getEngineeringmaterial().toString());
			table2.openCellRC(29, series).setValue(balancesheet.getDisposaloffixedassets().toString());
			table2.openCellRC(30, series).setValue(balancesheet.getProductivepiologicalasset().toString());
			table2.openCellRC(31, series).setValue(balancesheet.getOilandgasassets().toString());
			table2.openCellRC(32, series).setValue(balancesheet.getIntangibleassets().toString());
			table2.openCellRC(33, series).setValue(balancesheet.getDevelopmentexpenditure().toString());
			table2.openCellRC(34, series).setValue(balancesheet.getGoodwill().toString());
			table2.openCellRC(35, series).setValue(balancesheet.getLongTermunamortizedexpenses().toString());
			table2.openCellRC(36, series).setValue(balancesheet.getDeferredtaxassets().toString());
			table2.openCellRC(37, series).setValue(balancesheet.getOthernonCurrentassets().toString());
			table2.openCellRC(38, series).setValue(balancesheet.getTotalnonCurrentassets().toString());
			table2.openCellRC(39, series).setValue(balancesheet.getTotalassets().toString());
			//table2.openCellRC(40, series).setValue(balancesheet.getYear().toString());
			table2.openCellRC(41, series).setValue(balancesheet.getShortTermborrowing().toString());
			table2.openCellRC(42, series).setValue(balancesheet.getTransactionalfinancialliabilities().toString());
			table2.openCellRC(43, series).setValue(balancesheet.getNotespayable().toString());
			table2.openCellRC(44, series).setValue(balancesheet.getAccountspayable().toString());
			table2.openCellRC(45, series).setValue(balancesheet.getUnearnedrevenue().toString());
			table2.openCellRC(46, series).setValue(balancesheet.getPayrollpayable().toString());
			table2.openCellRC(47, series).setValue(balancesheet.getTaxpayable().toString());
			table2.openCellRC(48, series).setValue(balancesheet.getInterestpayable().toString());
			table2.openCellRC(49, series).setValue(balancesheet.getDividendspayable().toString());
			table2.openCellRC(50, series).setValue(balancesheet.getOtherpayables().toString());
			table2.openCellRC(51, series).setValue(balancesheet.getNonCurrentliability().toString());
			table2.openCellRC(52, series).setValue(balancesheet.getOthercurrentliability().toString());
			table2.openCellRC(53, series).setValue(balancesheet.getTotalcurrentliability().toString());
			//table2.openCellRC(54, series).setValue(balancesheet.getTotalcurrentliability().toString());
			table2.openCellRC(55, series).setValue(balancesheet.getLongTermloan().toString());
			table2.openCellRC(56, series).setValue(balancesheet.getTocopewithdebtsecurities().toString());
			table2.openCellRC(57, series).setValue(balancesheet.getLongTermloanpayable().toString());
			table2.openCellRC(58, series).setValue(balancesheet.getPayablespecial().toString());
			table2.openCellRC(59, series).setValue(balancesheet.getAnticipationliabilities().toString());
			table2.openCellRC(60, series).setValue(balancesheet.getDeferredincometaxliabilities().toString());
			table2.openCellRC(61, series).setValue(balancesheet.getOthernonCurrentliabilities().toString());
			table2.openCellRC(62, series).setValue(balancesheet.getTotalnonCurrentliabilities().toString());
			table2.openCellRC(63, series).setValue(balancesheet.getTotalliability().toString());
			//table2.openCellRC(64, series).setValue(balancesheet.getYear().toString());
			table2.openCellRC(65, series).setValue(balancesheet.getMinorityequity().toString());
			table2.openCellRC(66, series).setValue(balancesheet.getPaiclUpcapital().toString());
			table2.openCellRC(67, series).setValue(balancesheet.getCapitalreserve().toString());
			table2.openCellRC(68, series).setValue(balancesheet.getTreasuryshare().toString());
			table2.openCellRC(69, series).setValue(balancesheet.getEarnedsurplus().toString());
			table2.openCellRC(70, series).setValue(balancesheet.getUndistributedprofit().toString());
			table2.openCellRC(71, series).setValue(balancesheet.getTotalownersequity().toString());
			table2.openCellRC(72, series).setValue(balancesheet.getTotalliabilitiesandownersequity().toString());
		}
		
		
		
		
		//现金流量表
		DataRegion dataRegion8 = doc.openDataRegion("PO_FLOWS");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table3 = dataRegion8.openTable(1);
		List<flows> flowsList = flowsMapper.selectFlowsKey(id);
		
		for(int i = 0; i<flowsList.size(); i++) {
			//获取最远的一个年份
			flows flows = flowsList.get(i);
			//获取数据所在的列数
			int series = i+3;
			table3.openCellRC(1, series).setValue(flows.getYear().toString());
			//table3.openCellRC(2, series).setValue(flows.getYear().toString());
			table3.openCellRC(3, series).setValue(flows.getLaborcash().toString());
			table3.openCellRC(4, series).setValue(flows.getTaxrefund().toString());
			table3.openCellRC(5, series).setValue(flows.getOperatingcash().toString());
			table3.openCellRC(6, series).setValue(flows.getCashoutflowforoperatingactivities().toString());
			table3.openCellRC(7, series).setValue(flows.getCashforservicepayment().toString());
			table3.openCellRC(8, series).setValue(flows.getCashpaidforanemployee().toString());
			table3.openCellRC(9, series).setValue(flows.getTaxpayments().toString());
			table3.openCellRC(10, series).setValue(flows.getPaycashforbusinessactivities().toString());
			table3.openCellRC(11, series).setValue(flows.getActivitycashoutflowsubtotal().toString());
			table3.openCellRC(12, series).setValue(flows.getNetcashflowfromactivities().toString());
			//table3.openCellRC(13, series).setValue(flows.getYear().toString());
			table3.openCellRC(14, series).setValue(flows.getReturnoninvestment().toString());
			table3.openCellRC(15, series).setValue(flows.getGainoninvestment().toString());
			table3.openCellRC(16, series).setValue(flows.getCashrecoveredfromlongTermassets().toString());
			table3.openCellRC(17, series).setValue(flows.getHisbusinessunitreceivescash().toString());
			table3.openCellRC(18, series).setValue(flows.getReceiveothercashrelatedtoinvestmentactivities().toString());
			table3.openCellRC(19, series).setValue(flows.getCashsubtotalofinvestmentactivities().toString());
			table3.openCellRC(20, series).setValue(flows.getCashpaidbyotherlongTermassets().toString());
			table3.openCellRC(21, series).setValue(flows.getCashpaidforinvestments().toString());
			table3.openCellRC(22, series).setValue(flows.getCashpaidbythebusinessunit().toString());
			table3.openCellRC(23, series).setValue(flows.getCashrelatedtoinvestmentactivities().toString());
			table3.openCellRC(24, series).setValue(flows.getInvestmentcashoutflowsubtotal().toString());
			table3.openCellRC(25, series).setValue(flows.getThecashgeneratedbyaninvestment().toString());
			//table3.openCellRC(26, series).setValue(flows.getYear().toString());
			table3.openCellRC(27, series).setValue(flows.getAbsorbthecashreceivedfromtheinvestment().toString());
			table3.openCellRC(28, series).setValue(flows.getGetthecashteceivedfortheloan().toString());
			table3.openCellRC(29, series).setValue(flows.getReceivecashforfundraisingactivities().toString());
			table3.openCellRC(30, series).setValue(flows.getSubtotalofcashinflowfromfinancingactivities().toString());
			table3.openCellRC(31, series).setValue(flows.getPaymentofdebtsincash().toString());
			table3.openCellRC(32, series).setValue(flows.getDistributeprofit().toString());
			table3.openCellRC(33, series).setValue(flows.getPaycashforfundraisingactivities().toString());
			table3.openCellRC(34, series).setValue(flows.getSubtotalcashoutflowfromfinancingactivities().toString());
			table3.openCellRC(35, series).setValue(flows.getCashforfinancingactivities().toString());
			table3.openCellRC(36, series).setValue(flows.getExchangeratemovement().toString());
			table3.openCellRC(37, series).setValue(flows.getNetincreaseincash().toString());
			table3.openCellRC(38, series).setValue(flows.getBeginningcashbalance().toString());
			table3.openCellRC(39, series).setValue(flows.getEndingcashbalance().toString());
		}
		

		
		//利润表
		DataRegion dataRegion9 = doc.openDataRegion("PO_PROFIT");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table4 = dataRegion9.openTable(1);
		List<profit> profitList = profitMapper.selectProfitKey(id);
		
		for(int i = 0; i<profitList.size(); i++) {
			//获取最远的一个年份
			profit profit = profitList.get(i);
			//获取数据所在的列数
			int series = i+3;
			table4.openCellRC(1, series).setValue(profit.getYear().toString());
			table4.openCellRC(2, series).setValue(profit.getOperatingreceipt().toString());
			table4.openCellRC(3, series).setValue(profit.getMainbusinessincome().toString());
			table4.openCellRC(4, series).setValue(profit.getOperatingcosts().toString());
			table4.openCellRC(5, series).setValue(profit.getMainbusinesscost().toString());
			table4.openCellRC(6, series).setValue(profit.getBusinesstax().toString());
			table4.openCellRC(7, series).setValue(profit.getSellingexpenses().toString());
			table4.openCellRC(8, series).setValue(profit.getHandlingexpense().toString());
			table4.openCellRC(9, series).setValue(profit.getFinancialexpense().toString());
			table4.openCellRC(10, series).setValue(profit.getInterestexpense().toString());
			table4.openCellRC(11, series).setValue(profit.getAssetsimpairmentloss().toString());
			table4.openCellRC(12, series).setValue(profit.getFairvalue().toString());
			table4.openCellRC(13, series).setValue(profit.getIncomefrominvestment().toString());
			table4.openCellRC(14, series).setValue(profit.getInvestmentincomeofjointventure().toString());
			table4.openCellRC(15, series).setValue(profit.getOperatingprofit().toString());
			table4.openCellRC(16, series).setValue(profit.getNonbusinessincome().toString());
			table4.openCellRC(17, series).setValue(profit.getNonBusinessexpenditure().toString());
			table4.openCellRC(18, series).setValue(profit.getLossofnonCurrentassets().toString());
			table4.openCellRC(19, series).setValue(profit.getTotalprofit().toString());
			table4.openCellRC(20, series).setValue(profit.getIncometaxexpense().toString());
			table4.openCellRC(21, series).setValue(profit.getRetainedprofits().toString());
			table4.openCellRC(22, series).setValue(profit.getEarningspershare().toString());
			table4.openCellRC(23, series).setValue(profit.getBasicearningspershare().toString());
			table4.openCellRC(24, series).setValue(profit.getDilutedearningspershare().toString());
		}
		
		
		
		
		//获取行业标准
		RateVo r=new RateVo();
		r.setIndid(enterpriseInformation.getIndustryid());
		r.setType(enterpriseInformation.getScaleid());
		
		
		//盈利水平分析
		economicGain economicGain=economicGainMapper.selectEconomicGain(r);
		DataRegion dataRegion35 = doc.openDataRegion("PO_DEVELOPMENT");
		Table table26 = dataRegion35.openTable(1);
		table26.openCellRC(4, 5).setValue(economicGain.getCmarket().toString());
		table26.openCellRC(5, 5).setValue(economicGain.getCprofit().toString());
		table26.openCellRC(6, 5).setValue(economicGain.getChedging().toString());
		
		
		DataRegion dataRegion171 = doc.openDataRegion("PO_DEVELOPMENT1");
		development development = developmentMapper.selectDevelopment(id); 
		if(development == null) {
			dataRegion171.setValue("");
		}else {
			dataRegion171.setValue(development.getContent());
		}
		
		DataRegion dataRegion87 = doc.openDataRegion("PO_PATENTTYPEYEAR");
		DataRegion dataRegion88 = doc.openDataRegion("PO_PATENTTYPEYEAR1");
		DataRegion dataRegion89 = doc.openDataRegion("PO_PATENTTYPEYEAR2");
		DataRegion dataRegion90 = doc.openDataRegion("PO_PATENTTYPEYEAR3");
		DataRegion dataRegion91 = doc.openDataRegion("PO_QUALIFICATION1");
		DataRegion dataRegion92 = doc.openDataRegion("PO_QUALIFICATION2");
		DataRegion dataRegion93 = doc.openDataRegion("PO_QUALIFICATION3");
		DataRegion dataRegion94 = doc.openDataRegion("PO_QUALIFICATION4");
		DataRegion dataRegion95 = doc.openDataRegion("PO_QUALIFICATION5");
		DataRegion dataRegion96 = doc.openDataRegion("PO_QUALIFICATION6");
		DataRegion dataRegion97 = doc.openDataRegion("PO_QUALIFICATION7");
		DataRegion dataRegion98 = doc.openDataRegion("PO_QUALIFICATION8");
		DataRegion dataRegion99 = doc.openDataRegion("PO_PATENTTYPEYEAR4");
		DataRegion dataRegion100 = doc.openDataRegion("PO_PATENTTYPEYEAR5");
		DataRegion dataRegion101 = doc.openDataRegion("PO_PATENTTYPEYEAR6");
		DataRegion dataRegion102 = doc.openDataRegion("PO_PATENTTYPEYEAR7");
		DataRegion dataRegion103 = doc.openDataRegion("PO_PATENTTYPEYEAR8");
		DataRegion dataRegion104 = doc.openDataRegion("PO_PATENTTYPEYEAR9");
		DataRegion dataRegion105 = doc.openDataRegion("PO_PATENTTYPEYEAR10");
		DataRegion dataRegion106 = doc.openDataRegion("PO_PATENTTYPEYEAR11");
		DataRegion dataRegion107 = doc.openDataRegion("PO_PATENTTYPEYEAR12");
		DataRegion dataRegion108 = doc.openDataRegion("PO_PATENTTYPEYEAR13");
		DataRegion dataRegion109 = doc.openDataRegion("PO_PATENTTYPEYEAR14");
		DataRegion dataRegion110 = doc.openDataRegion("PO_PATENTTYPEYEAR15");
		DataRegion dataRegion111 = doc.openDataRegion("PO_PATENTTYPEYEAR16");
		DataRegion dataRegion112 = doc.openDataRegion("PO_PATENTTYPEYEAR17");


		int devel1 = 0;
		int devel2 = 0;
		int devel3 = 0;
		

		//负债水平分析
		debtRisk debtRisk = debtRiskMapper.selectDebtRisk(r);
		DataRegion dataRegion40 = doc.openDataRegion("PO_DEBTRISK");
		Table table28 = dataRegion40.openTable(1);
		table28.openCellRC(4, 5).setValue(debtRisk.getCloadrate().toString());
		table28.openCellRC(5, 5).setValue(debtRisk.getCquickaction().toString());
		table28.openCellRC(6, 5).setValue(debtRisk.getCflow().toString());
		DataRegion dataRegion113 = doc.openDataRegion("PO_DEBTRISK1");
		DataRegion dataRegion114 = doc.openDataRegion("PO_DEBTRISK2");
		DataRegion dataRegion115 = doc.openDataRegion("PO_DEBTRISK3");
		
		//现金流量分析
		DataRegion dataRegion41 = doc.openDataRegion("PO_FLOWS1");
		Table table29 = dataRegion41.openTable(1);
		DataRegion dataRegion116 = doc.openDataRegion("PO_FLOWS2");
		DataRegion dataRegion117 = doc.openDataRegion("PO_FLOWS3");
		DataRegion dataRegion118 = doc.openDataRegion("PO_FLOWS4");
		DataRegion dataRegion119 = doc.openDataRegion("PO_FLOWS5");
		DataRegion dataRegion120 = doc.openDataRegion("PO_FLOWS6");
		DataRegion dataRegion121 = doc.openDataRegion("PO_FLOWS7");
		DataRegion dataRegion122 = doc.openDataRegion("PO_FLOWS8");
		DataRegion dataRegion123 = doc.openDataRegion("PO_FLOWS9");
		DataRegion dataRegion124 = doc.openDataRegion("PO_FLOWS10");
		
		//资产水平分析
		qualityAssets qualityAssets = qualityAssetsMapper.selectQualityAssets(r);
		DataRegion dataRegion42 = doc.openDataRegion("PO_ASSET");
		Table table30 = dataRegion42.openTable(1);
		table30.openCellRC(4, 5).setValue(qualityAssets.getCtotalassets().toString());
		table30.openCellRC(5, 5).setValue(qualityAssets.getCreceivables().toString());
		table30.openCellRC(6, 5).setValue(qualityAssets.getCflow().toString());
		
		DataRegion dataRegion125 = doc.openDataRegion("PO_ASSET1");
		DataRegion dataRegion126 = doc.openDataRegion("PO_ASSET2");
		DataRegion dataRegion127 = doc.openDataRegion("PO_ASSET3");
		DataRegion dataRegion128 = doc.openDataRegion("PO_ASSET4");
		DataRegion dataRegion129 = doc.openDataRegion("PO_ASSET5");
		DataRegion dataRegion130 = doc.openDataRegion("PO_ASSET6");
		DataRegion dataRegion131 = doc.openDataRegion("PO_ASSET7");
		DataRegion dataRegion132 = doc.openDataRegion("PO_ASSET8");
		DataRegion dataRegion133 = doc.openDataRegion("PO_ASSET9");
		
		
		
		
		
		//财务效益状况
		DataRegion dataRegion43 = doc.openDataRegion("PO_FINANCE");
		Table table31 = dataRegion43.openTable(1);
		profitability profitability = profitabilityMapper.selectProfitability(r);
		table31.openCellRC(2, 5).setValue(profitability.getCnetasset().toString());
		table31.openCellRC(3, 5).setValue(profitability.getCmarket().toString());
		table31.openCellRC(4, 5).setValue(profitability.getCcost().toString());
		table31.openCellRC(5, 5).setValue(profitability.getCtotalassets().toString());
		table31.openCellRC(6, 5).setValue(profitability.getCcapital().toString());
		
		DataRegion dataRegion134 = doc.openDataRegion("PO_FINANCE1");
		DataRegion dataRegion135 = doc.openDataRegion("PO_FINANCE2");
		DataRegion dataRegion136 = doc.openDataRegion("PO_FINANCE3");
		DataRegion dataRegion137 = doc.openDataRegion("PO_FINANCE4");
		DataRegion dataRegion138 = doc.openDataRegion("PO_FINANCE5");
		DataRegion dataRegion139 = doc.openDataRegion("PO_FINANCE6");
		DataRegion dataRegion140 = doc.openDataRegion("PO_FINANCE7");
		DataRegion dataRegion141 = doc.openDataRegion("PO_FINANCE8");
		DataRegion dataRegion142 = doc.openDataRegion("PO_FINANCE9");
		DataRegion dataRegion143 = doc.openDataRegion("PO_FINANCE10");
		DataRegion dataRegion144 = doc.openDataRegion("PO_FINANCE11");
		DataRegion dataRegion145 = doc.openDataRegion("PO_FINANCE12");
		
		
		DataRegion dataRegion10 = doc.openDataRegion("PO_THE_MAIN_FINANCIAL");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table5 = dataRegion10.openTable(1);
		BigDecimal c = new BigDecimal(100);
		BigDecimal a = new BigDecimal(2);
		BigDecimal b =new BigDecimal(10000);
		BigDecimal num;
		StringBuffer count =new StringBuffer();
		StringBuffer count1 =new StringBuffer();
		StringBuffer count2 =new StringBuffer();
		for(int i = 0; i<profitList.size(); i++) {
			//获取最远的一个年份
			profit profit = profitList.get(i);
			flows flows = flowsList.get(i);
			balanceSheet balancesheet = balanceSheetList.get(i);
			//获取数据所在的列数
			int series = i+2;
			table5.openCellRC(1, series).setValue(profit.getYear().toString());
			
			
			//流动负债合计 * 100   /  经营活动产生的现金流量净额  = 现金流动负债比率(%)
			if(balancesheet.getTotalcurrentliability().doubleValue() == 0) {
				num=balancesheet.getTotalcurrentliability();
			}else {
				num=flows.getNetcashflowfromactivities().multiply(c)
		                   .divide(balancesheet.getTotalcurrentliability(), 2, RoundingMode.HALF_UP);
			}
			
			table5.openCellRC(3, series).setValue(num.toString());
			table28.openCellRC(6, i+2).setValue(num.toString());
			if(2==i) {
				devel1=0;
				count =new StringBuffer();
				dataRegion117.setValue(num.toString());
                if(num.doubleValue() > debtRisk.getCflow().doubleValue()) {
                	dataRegion118.setValue("高于行业平均水平，说明公司经营活动产生的净现金流对流动负债的保障能力较强。");
                	count1.append("从现金流动角度来看，公司经营活动产生的现金净流量高于行业平均水平，偿付短期负债的能力较强");
                }else if(num.doubleValue() >= debtRisk.getCflow().doubleValue()-2) {
                	dataRegion118.setValue("基本持平于行业平均水平，说明公司经营活动产生的净现金流对流动负债的保障能力一般。");
                	count1.append("从现金流动角度来看，公司经营活动产生的现金净流量基本持平于行业平均水平，偿付短期负债的能力一般");
                	devel1=1;
                }else {
                	dataRegion118.setValue("低于行业平均水平，说明公司经营活动产生的净现金流对流动负债的保障能力欠佳。");
                	count1.append("从现金流动角度来看，公司经营活动产生的现金净流量低于行业平均水平，偿付短期负债的能力偏弱");
                	devel1=2;
                }
				
			}
			
			
			//总负债 / 总资产  * 100 = 资产负债率(%)
			if(balancesheet.getTotalassets().doubleValue() == 0) {
				num=balancesheet.getTotalassets();
			}else {
				num=balancesheet.getTotalliability().multiply(c)
	                    .divide(balancesheet.getTotalassets(), 2, RoundingMode.HALF_UP);
			}
			table5.openCellRC(2, series).setValue(num.toString());
			table28.openCellRC(1, i+2).setValue(profit.getYear().toString());
			table28.openCellRC(2, i+2).setValue(balancesheet.getTotalliability().divide(b).toString());
			table28.openCellRC(3, i+2).setValue(balancesheet.getTotalcurrentliability().divide(b).toString());
			table28.openCellRC(4, i+2).setValue(num.toString());
			//现金流量分析
			if(2==i) {
				devel2=0;
				dataRegion115.setValue(balancesheet.getTotalliability().divide(b).toString());
				dataRegion113.setValue(balancesheet.getTotalcurrentliability().divide(b).toString());
				dataRegion114.setValue(balancesheet.getTotalcurrentliability().multiply(c)
							.divide(balancesheet.getTotalliability(), 2, RoundingMode.HALF_UP).toString());

				
				
				if(flows.getNetcashflowfromactivities().doubleValue() == 0) {
					dataRegion116.setValue("0");
				}else {
					dataRegion116.setValue(flows.getNetcashflowfromactivities().toString());   //现金流量净额
				}
				dataRegion119.setValue(num.toString());
				if(num.doubleValue() > debtRisk.getCloadrate().doubleValue()) {
					dataRegion120.setValue("低于行业平均水平，反映公司债务压力较大。");
					count1.append("资产负债率低于行业平均值，反映公司总体负债结构良好,资产对债务的保护能力较强；");
					devel2=2;
				}else if(num.doubleValue() <= debtRisk.getCloadrate().doubleValue()+2 && num.doubleValue() >= debtRisk.getCloadrate().doubleValue()) {
					dataRegion120.setValue("基本持平于行业平均水平，反映公司债务压力一般。");
					count1.append("资产负债率基本持平于行业平均值，反映公司总体负债结构尚可,资产对债务的保护能力一般；");
					devel2=1;
				}else {
					dataRegion120.setValue("高于行业平均水平，反映公司债务压力较小。");
					count1.append("资产负债率高于行业平均值，反映公司总体负债结构不合理,资产对债务的保护能力偏弱；");
				}
				
			}
			
			
			// 流动负债合计 * 100  /  流动资产合计 - 一年内到期的非流动资产 = 速动比率(%)
            if(balancesheet.getTotalcurrentliability().doubleValue() == 0) {
            	num=balancesheet.getTotalcurrentliability();
            }else {
            	num=balancesheet.getTotalcurrentassets().subtract(balancesheet.getInventory()).multiply(c)
    					.divide(balancesheet.getTotalcurrentliability(), 2, RoundingMode.HALF_UP);
            }
			

			table5.openCellRC(4, series).setValue(num.toString());
			table28.openCellRC(5, i+2).setValue(num.toString());
			
			if(2==i) {
				devel3=0;
				dataRegion121.setValue(num.toString());
				if(num.doubleValue() > debtRisk.getCquickaction().doubleValue()) {
					dataRegion122.setValue("高于行业平均水平，反映公司短期偿债能力较强。");
					count1.append("速动比率高于行业平均值，反映公司流动资产中可以立即变现用于偿还流动负债的能力较强；");
				}else if(num.doubleValue() >= debtRisk.getCquickaction().doubleValue()-2) {
					dataRegion122.setValue("基本持平于行业平均水平，反映公司短期偿债能力一般。");
					count1.append("速动比率基本持平于行业平均值，反映公司流动资产中可以立即变现用于偿还流动负债的能力一般 ；");
					devel3=1;
				}else {
					dataRegion122.setValue("低于行业平均水平，反映公司短期偿债能力偏低。");
					count1.append("速动比率低于行业平均值，反映公司流动资产中可以立即变现用于偿还流动负债的能力偏弱；");
					devel3=2;
				}
				dataRegion123.setValue(count1.toString());
				dataRegion160.setValue(count1.toString());
				
				if (devel1 == 0 && devel2 == 0 && devel3 != 2 || devel1 == 0 && devel3 == 0 && devel2 != 2
						|| devel2 == 0 && devel3 == 0 && devel1 != 2) {  //俩个较强且没有较弱
					dataRegion124.setValue("状况较好");
				} else if (devel1 == 0 && devel2 == 2 && devel3 != 2 || devel1 == 0 && devel3 == 2 && devel2 != 2
						|| devel2 == 0 && devel3 == 2 && devel1 != 2 || devel2 == 0 && devel1 == 2 && devel3 != 2
						|| devel3 == 0 && devel1 == 2 && devel2 != 2 || devel3 == 0 && devel2 == 2 && devel1 != 2
					    || devel3 == 1 && devel1 == 1 && devel2 != 1) { //至多1个较强且至多一个偏弱，或是无较强和偏弱
					dataRegion124.setValue("一般");
				} else {
					dataRegion124.setValue("不佳");
				}
				
				
			}
			
			
			
			//营业收入 / 总资产  * 100 = 总资产周转率(次)
			if(i == 0) {
				if(balancesheet.getTotalassets().doubleValue() == 0) {
					num=balancesheet.getTotalassets();
				}else {
					num=profit.getOperatingreceipt()
		                    .divide(balancesheet.getTotalassets(), 2, RoundingMode.HALF_UP);
				}
				
			}else {
				balanceSheet balancesheet1 = balanceSheetList.get(i-1);
				if(balancesheet.getTotalassets().add(balancesheet1.getTotalassets()).doubleValue() == 0) {
					num=balancesheet.getTotalassets().add(balancesheet1.getTotalassets());
				}else {
					num=profit.getOperatingreceipt()
		                    .divide(balancesheet.getTotalassets().add(balancesheet1.getTotalassets()).divide(a), 2, RoundingMode.HALF_UP);
				}
				
			}
			
			table5.openCellRC(5, series).setValue(num.toString());
			table30.openCellRC(1, i+2).setValue(profit.getYear().toString());
			table30.openCellRC(2, i+2).setValue(balancesheet.getTotalassets().divide(b).toString());
			table30.openCellRC(3, i+2).setValue(balancesheet.getTotalcurrentassets().divide(b).toString());
			table30.openCellRC(4, i+2).setValue(num.toString());
			
			//资产水平分析
			if(i == 2) {
				devel1=0;
				dataRegion125.setValue(balancesheet.getTotalassets().divide(b).toString());
				dataRegion126.setValue(balancesheet.getTotalcurrentassets().divide(b).toString());
				if(balancesheet.getTotalassets().doubleValue() == 0) {
					dataRegion127.setValue("0");
				}else {
					dataRegion127.setValue(balancesheet.getTotalcurrentassets().multiply(c)
							.divide(balancesheet.getTotalassets(), 2, RoundingMode.HALF_UP).toString());
				}
				
				
				if(num.doubleValue() > qualityAssets.getCtotalassets().doubleValue()) {
					dataRegion132.setValue("公司总资产周转速度较快，资产的经营质量好；公司应收账款的收款速度较快；流动资产的利用率较好。");
					dataRegion161.setValue("公司总资产周转速度较快，资产的经营质量好；公司应收账款的收款速度较快；流动资产的利用率较好。");
				}else if(num.doubleValue() > qualityAssets.getCtotalassets().doubleValue()-2) {
					dataRegion132.setValue("公司总资产周转速度较一般，资产的经营质尚可；公司应收账款的收款速度较一般；流动资产的利用率尚可。");
					dataRegion161.setValue("公司总资产周转速度较一般，资产的经营质尚可；公司应收账款的收款速度较一般；流动资产的利用率尚可。");
					devel1=1;
				}else {
					dataRegion132.setValue("公司总资产周转速度较慢，资产的经营质量不佳；公司应收账款的收款速度较慢；流动资产的利用率较低。");
					dataRegion161.setValue("公司总资产周转速度较慢，资产的经营质量不佳；公司应收账款的收款速度较慢；流动资产的利用率较低。");
					devel1=2;
				}
			}
			
			
			
			
			//(应收账款(今年+去年)) / 2 * 100  /  营业收入  = 应收账款周转率
			if(i == 0) {
				if(balancesheet.getReceivables().doubleValue() == 0) {
					num=balancesheet.getReceivables();
				}else {
					//i=0第一次循环年份最远   不需要累加求平均
					num=profit.getOperatingreceipt()
							.divide(balancesheet.getReceivables(), 2, RoundingMode.HALF_UP);
				}
				
			}else{
				balanceSheet balancesheet1 = balanceSheetList.get(i-1);
				if(balancesheet.getReceivables().add(balancesheet1.getReceivables()).divide(a).doubleValue() == 0) {
					num=balancesheet.getReceivables();
				}else {
					num=profit.getOperatingreceipt()
							.divide(balancesheet.getReceivables().add(balancesheet1.getReceivables()).divide(a), 2, RoundingMode.HALF_UP);
				}
			}
			table5.openCellRC(6, series).setValue(num.toString());
			table30.openCellRC(5, i+2).setValue(num.toString());
			
			if(i == 2) {
				devel2=0;
				dataRegion128.setValue(num.toString());
				if(num.doubleValue() > qualityAssets.getCreceivables().doubleValue()) {
					dataRegion129.setValue("高于行业平均水平，反映公司应收账款的收账速度较快。");
				}else if(num.doubleValue() > qualityAssets.getCreceivables().doubleValue()-2) {
					dataRegion129.setValue("基本持平于行业平均水平，反映公司应收账款的收账速度较一般。");
					devel2=1;
				}else {
					dataRegion129.setValue("低于行业平均水平，反映公司应收账款的收账速度较较慢。");
					devel2=2;
				}
			}
			
			
			
			//流动资产合计 (今年+去年)) / 2 * 100  /  营业收入   = 流动资产周转率
			if(i == 0) {
				//i=0第一次循环年份最远   不需要累加求平均
				if(balancesheet.getTotalcurrentassets().doubleValue() == 0) {
					num=balancesheet.getTotalcurrentassets();
				}else {
					num=profit.getOperatingreceipt()
							.divide(balancesheet.getTotalcurrentassets(), 2, RoundingMode.HALF_UP);
				}
				
			}else{
				balanceSheet balancesheet1 = balanceSheetList.get(i-1);
				if(balancesheet.getTotalcurrentassets().add(balancesheet1.getTotalcurrentassets()).doubleValue() == 0) {
					num=balancesheet.getTotalcurrentassets().add(balancesheet1.getTotalcurrentassets());
				}else {
					num=profit.getOperatingreceipt()
							.divide(balancesheet.getTotalcurrentassets().add(balancesheet1.getTotalcurrentassets()).divide(a), 2, RoundingMode.HALF_UP);
				}
				
			}
			table5.openCellRC(7, series).setValue(num.toString());
			table30.openCellRC(6, i+2).setValue(num.toString());
			
			if(i == 2) {
				devel3=0;
				dataRegion130.setValue(num.toString());
				if(num.doubleValue() > qualityAssets.getCflow().doubleValue()) {
					dataRegion131.setValue("高于行业平均水平，反映公司流动资产周转率速度较快，流动资产利用率较好。");
				}else if(num.doubleValue() > qualityAssets.getCflow().doubleValue()-2) {
					dataRegion131.setValue("基本持平于行业平均水平，反映公司流动资产周转率速度一般，流动资产利用率尚可。");
					devel3=1;
				}else {
					dataRegion131.setValue("低于行业平均水平，反映公司流动资产周转率速度较慢，流动资产利用率较好较低。");
					devel3=2;
				}
				
				if (devel1 == 0 && devel2 == 0 && devel3 != 2 || devel1 == 0 && devel3 == 0 && devel2 != 2
						|| devel2 == 0 && devel3 == 0 && devel1 != 2) {  //俩个较强且没有较弱
					dataRegion133.setValue("状况较好");
				} else if (devel1 == 0 && devel2 == 2 && devel3 != 2 || devel1 == 0 && devel3 == 2 && devel2 != 2
						|| devel2 == 0 && devel3 == 2 && devel1 != 2 || devel2 == 0 && devel1 == 2 && devel3 != 2
						|| devel3 == 0 && devel1 == 2 && devel2 != 2 || devel3 == 0 && devel2 == 2 && devel1 != 2
					    || devel3 == 1 && devel1 == 1 && devel2 != 1) { //至多1个较强且至多一个偏弱，或是无较强和偏弱
					dataRegion133.setValue("一般");
				} else {
					dataRegion133.setValue("较差");
				}
			}
			
			
			
			
			//所有者权益（或股东权益）合计(今年+去年) / 2  * 100 / 净利润  = 净资产收益率(%)
			if(i == 0) {
				//i=0第一次循环年份最远   不需要累加求平均
				if(balancesheet.getTotalownersequity().doubleValue() == 0) {
					num=balancesheet.getTotalownersequity();
				}else {
					num=profit.getRetainedprofits().multiply(c)
							.divide(balancesheet.getTotalownersequity(), 2, RoundingMode.HALF_UP);
				}
				
			}else{
				balanceSheet balancesheet1 = balanceSheetList.get(i-1);
				if(balancesheet.getTotalownersequity().add(balancesheet1.getTotalownersequity()).doubleValue() == 0) {
					num=balancesheet.getTotalownersequity().add(balancesheet1.getTotalownersequity());
				}else {
					num=profit.getRetainedprofits().multiply(c)
							.divide(balancesheet.getTotalownersequity().add(balancesheet1.getTotalownersequity()).divide(a), 2, RoundingMode.HALF_UP);
				}
				
			}
			table5.openCellRC(8, series).setValue(num.toString());
			table31.openCellRC(1, i+2).setValue(profit.getYear().toString());
			table31.openCellRC(2, i+2).setValue(num.toString());
			
			//财务效益情况
			if(i == 2) {
				devel1=0;
				dataRegion134.setValue(num.toString());
				if(num.doubleValue() > profitability.getCnetasset().doubleValue()) {
					dataRegion135.setValue("高于行业平均值，反映公司运用净资产的获利能力较强。");
					count2.append("公司运用净资产的获利能力较强；");
				}else if(num.doubleValue() > profitability.getCnetasset().doubleValue()-2) {
					dataRegion135.setValue("基本持平于行业平均值，反映公司运用净资产的获利能力一般。");
					devel1=1;
					count2.append("公司运用净资产的获利能力一般；");
				}else {
					dataRegion135.setValue("低于行业平均值，反映公司运用净资产的获利能力偏弱。");
					devel1=2;
					count2.append("公司运用净资产的获利能力偏弱；");
				}
			}
			
			
			
			
			
			//经营活动产生的现金流量 *100  / 营业利润（损失以“-”号填列）  = 营业收入利润率(%)
			if(profit.getOperatingreceipt().doubleValue() == 0) {
				num=profit.getOperatingreceipt();
			}else {
				num=profit.getOperatingreceipt().subtract(profit.getOperatingcosts()).subtract(profit.getBusinesstax()).subtract(profit.getSellingexpenses())
					      .subtract(profit.getHandlingexpense()).subtract(profit.getFinancialexpense()).subtract(profit.getAssetsimpairmentloss())
					      .add(profit.getFairvalue()).add(profit.getIncomefrominvestment()).add(profit.getNonbusinessincome()).multiply(c)
					      .divide(profit.getOperatingreceipt(), 2, RoundingMode.HALF_UP);
			}
			
			
			
			table5.openCellRC(9, series).setValue(num.toString());
			
			if(i == 2) {
				dataRegion136.setValue(num.toString());
				if(num.doubleValue() > profitability.getCmarket().doubleValue()) {
					dataRegion137.setValue("高于行业平均值，反映公司主营业务盈利能力较强。");
				}else if(num.doubleValue() > profitability.getCmarket().doubleValue()-2) {
					dataRegion137.setValue("基本持平于行业平均值，反映公司主营业务盈利能力一般。");
				}else {
					dataRegion137.setValue("低于行业平均值，反映公司主营业务盈利能力偏弱。");
				}
			}
			
			
			//经营活动产生的现金流量 *100 /  经营活动现金流入小计  =  主营收入现金率(%)
			if(profit.getMainbusinessincome().doubleValue() == 0) {
				num=profit.getMainbusinessincome();
			}else {
				num=flows.getLaborcash().multiply(c)
						.divide(profit.getMainbusinessincome(), 2, RoundingMode.HALF_UP);
			}
			
			table5.openCellRC(10, series).setValue(num.toString());
			table31.openCellRC(4, i+2).setValue(num.toString());
			
			if(i == 2) {
				dataRegion138.setValue(num.toString());
				if(num.doubleValue() > profitability.getCcost().doubleValue()) {
					dataRegion139.setValue("高于行业平均值，反映公司经营耗费获取利润的能力较强。");
				}else if(num.doubleValue() > profitability.getCcost().doubleValue()-2) {
					dataRegion139.setValue("基本持平于行业平均值，反映公司经营耗费获取利润的能力一般。");
				}else {
					dataRegion139.setValue("低于行业平均值，反映公司经营耗费获取利润的能力偏弱。");
				}
				
			}
			
			
			
			
			//资产总计(今年+去年) / 2  * 100  /  (利润总额（损失以“-”号填列） + 其中：利息支出)  =  总资产报酬率
			if(i == 0) {
				if(balancesheet.getTotalassets().doubleValue() == 0) {
					num=balancesheet.getTotalassets();
				}else {
					num=profit.getTotalprofit().add(profit.getInterestexpense()).multiply(c)
							.divide(balancesheet.getTotalassets(), 2, RoundingMode.HALF_UP);
				}
				
			}else {
				balanceSheet balancesheet1 = balanceSheetList.get(i-1);
				if(balancesheet.getTotalassets().add(balancesheet1.getTotalassets()).doubleValue() == 0) {
					num=balancesheet.getTotalassets().add(balancesheet1.getTotalassets());
				}else {
					num=profit.getTotalprofit().add(profit.getInterestexpense()).multiply(c)
							.divide(balancesheet.getTotalassets().add(balancesheet1.getTotalassets()).divide(a), 2, RoundingMode.HALF_UP);
				}
				
			}
			table5.openCellRC(11, series).setValue(num.toString());
			table31.openCellRC(5, i+2).setValue(num.toString());
			
			if(i == 2) {
				devel2=0;
				dataRegion140.setValue(num.toString());
				if(num.doubleValue() > profitability.getCtotalassets().doubleValue()) {
					dataRegion141.setValue("高于行业平均值，反映公司投入产出的水平较强。");
					count2.append("投入产出的水平一般；");
				}else if(num.doubleValue() > profitability.getCtotalassets().doubleValue()-2) {
					dataRegion141.setValue("基本持平于行业平均值，反映公司投入产出的水平一般。");
					devel2=1;
					count2.append("投入产出的水平一般；");
				}else {
					dataRegion141.setValue("低于行业平均值，反映公司投入产出的水平偏弱。");
					devel2=2;
					count2.append("投入产出的水平一般；");
				}
			}
			
			
			
			
			//所有者权益（或股东权益）合计  * 100  /所有者权益（或股东权益）合计(今年-去年)  =  资本积累率(%)
			if(i == 0) {
				num=new BigDecimal(100.00);
			}else {
				if(balancesheet.getTotalownersequity().doubleValue() == 0) {
					num=balancesheet.getTotalownersequity();
				}else {
					balanceSheet balancesheet1 = balanceSheetList.get(i-1);
					num=balancesheet.getTotalownersequity().subtract(balancesheet1.getTotalownersequity()).multiply(c)
							.divide(balancesheet.getTotalownersequity(), 2, RoundingMode.HALF_UP);
				}
				
			}
			table5.openCellRC(12, series).setValue(num.toString());
			table31.openCellRC(6, i+2).setValue(num.toString());

			if(i == 2) {
				devel3=0;
				dataRegion142.setValue(num.toString());
				if(num.doubleValue() > profitability.getCtotalassets().doubleValue()) {
					dataRegion143.setValue("高于行业平均值，反映公司成本费用控制能力较强。");
					count2.append("成本费用控制能力一般；");
				}else if(num.doubleValue() > profitability.getCtotalassets().doubleValue()-2) {
					dataRegion143.setValue("基本持平于行业平均值，反映公司成本费用控制能力一般。");
					devel3=1;
					count2.append("成本费用控制能力一般；");
				}else {
					dataRegion143.setValue("低于行业平均值，反映公司成本费用控制能力偏弱。");
					devel3=2;
					count2.append("成本费用控制能力一般；");
				}
				dataRegion144.setValue(count2.toString());
				dataRegion162.setValue(count2.toString());
				
				if (devel1 == 0 && devel2 == 0 && devel3 != 2 || devel1 == 0 && devel3 == 0 && devel2 != 2
						|| devel2 == 0 && devel3 == 0 && devel1 != 2) {  //俩个较强且没有较弱
					dataRegion145.setValue("公司运用净资产的获利能力较强；投入产出的水平较强；成本费用控制能力较强。总体来看，公司财务效益状况状况较好");
				} else if (devel1 == 0 && devel2 == 2 && devel3 != 2 || devel1 == 0 && devel3 == 2 && devel2 != 2
						|| devel2 == 0 && devel3 == 2 && devel1 != 2 || devel2 == 0 && devel1 == 2 && devel3 != 2
						|| devel3 == 0 && devel1 == 2 && devel2 != 2 || devel3 == 0 && devel2 == 2 && devel1 != 2
					    || devel3 == 1 && devel1 == 1 && devel2 != 1) { //至多1个较强且至多一个偏弱，或是无较强和偏弱
					dataRegion145.setValue("公司运用净资产的获利能力一般；投入产出的水平一般；成本费用控制能力一般。总体来看，公司财务效益状况一般");
				} else {
					dataRegion145.setValue("公司运用净资产的获利能力偏弱；投入产出的水平偏弱；成本费用控制能力偏弱。总体来看，公司财务效益状况较差");
				}
			}
			
			
			//去年营业收入 / 今年 - 去年  = 营业收入增长率
			if(i == 0) {
				num=new BigDecimal(0.00);
			}else {
				
				profit profit1 = profitList.get(i-1); 
				if(profit1.getOperatingreceipt().doubleValue() == 0) {
					num=new BigDecimal(0.00);
				}else {
					num=profit.getOperatingreceipt().subtract(profit1.getOperatingreceipt()).multiply(c)
							.divide(profit1.getOperatingreceipt(), 2, RoundingMode.HALF_UP);
				}
				
			}
			table5.openCellRC(13, series).setValue(num.toString());
			table26.openCellRC(1, i+2).setValue(profit.getYear().toString());
			table26.openCellRC(2, i+2).setValue(profit.getOperatingreceipt().divide(b).toString());
			table26.openCellRC(3, i+2).setValue(profit.getMainbusinessincome().divide(b).toString());
			table26.openCellRC(4, i+2).setValue(num.toString());
			//获取最近年份
			if(2==i){
				devel1=0;
				dataRegion87.setValue(profit.getYear().toString());
				dataRegion88.setValue(profit.getYear().toString());
				dataRegion89.setValue(profit.getYear().toString());
				dataRegion90.setValue(profit.getYear().toString());
				dataRegion99.setValue(profit.getYear().toString());
				dataRegion100.setValue(profit.getYear().toString());
				dataRegion101.setValue(profit.getYear().toString());
				dataRegion102.setValue(profit.getYear().toString());
				dataRegion103.setValue(profit.getYear().toString());
				dataRegion104.setValue(profit.getYear().toString());
				dataRegion105.setValue(profit.getYear().toString());
				dataRegion106.setValue(profit.getYear().toString());
				dataRegion107.setValue(profit.getYear().toString());
				dataRegion108.setValue(profit.getYear().toString());
				dataRegion109.setValue(profit.getYear().toString());
				dataRegion110.setValue(profit.getYear().toString());
				dataRegion111.setValue(profit.getYear().toString());
				dataRegion112.setValue(profit.getYear().toString());
				dataRegion91.setValue(num.toString());
				if(num.doubleValue() > economicGain.getCmarket().doubleValue()) {
					dataRegion92.setValue("高于行业平均值，反映公司营业收入增长较快，经营状况好转，市场占有能力较强。");
					count.append("公司营业收入增长率高于行业平均值，反映公司营业收入增长较快，经营状况好转，市场占有能力较强。");
				}else if(num.doubleValue() >= economicGain.getCmarket().doubleValue()-2) {
					dataRegion92.setValue("基本持平于行业平均值，反映公司营业收入增长较一般，经营状况良好，市场占有能力尚可。");
					count.append("公司营业收入增长率基本持平于行业平均值，反映公司营业收入增长较一般，经营状况良好，市场占有能力尚可。");
					devel1=1;
				}else {
					dataRegion92.setValue("低于行业平均值，反映公司营业收入增长较慢，经营状况不佳，市场占有能力较差。");	
					count.append("公司营业收入增长率低于行业平均值，反映公司营业收入增长较慢，经营状况不佳，市场占有能力较差。");
					devel1=2;
				}
			}
			
			
			//去年营业利润（损失以“-”号填列）  / 今年 - 去年  = 营业利润增长率 
			if(i == 0) {
				num=new BigDecimal(0.00);
			}else {
				profit profit1 = profitList.get(i-1); 
				if(profit1.getOperatingreceipt().doubleValue() == 0  || profit1.getOperatingcosts().doubleValue() == 0 || profit1.getBusinesstax().doubleValue() == 0) {
					num=new BigDecimal(0.00);
				}else {
					if(profit1.getOperatingreceipt().subtract(profit1.getOperatingcosts()).subtract(profit1.getBusinesstax()).doubleValue() == 0) {
						num=new BigDecimal(0.00);
					}else {
						num=(profit.getOperatingreceipt().subtract(profit.getOperatingcosts()).subtract(profit.getBusinesstax())).
								   subtract(profit1.getOperatingreceipt().subtract(profit1.getOperatingcosts()).subtract(profit1.getBusinesstax())).multiply(c)
								   .divide(profit1.getOperatingreceipt().subtract(profit1.getOperatingcosts()).subtract(profit1.getBusinesstax()), 2, RoundingMode.HALF_UP);
					}
						
				}
				
			}
			table5.openCellRC(14, series).setValue(num.toString());
			table26.openCellRC(5, i+2).setValue(num.toString());
			if(2==i){
				devel2=0;
				dataRegion93.setValue(num.toString());
				if(num.doubleValue() > economicGain.getCprofit().doubleValue()) {
					dataRegion94.setValue("高于行业平均值，反映公司盈利能力较好。");
					count.append("公司营业利润增长率高于行业平均值，反映公司盈利能力较好。");
				}else if(num.doubleValue() >= economicGain.getCprofit().doubleValue()-2) {
					dataRegion94.setValue("基本持平于行业平均值，反映公司盈利能力一般。");
					count.append("公司营业利润增长率基本持平于行业平均值，反映公司盈利能力一般。");
					devel2=1;
				}else {
					dataRegion94.setValue("低于行业平均值，反映公司盈利能力不佳。");	
					count.append("公司营业利润增长率低于行业平均值，反映公司盈利能力不佳。");
					devel2=2;
				}
			}
			
			//去年 利润总额 / 今年利润总额  = 资本保值增长率
			if(i == 0) {
				num=new BigDecimal(0.00);
			}else {
				balanceSheet balancesheet1 = balanceSheetList.get(i-1);
				if(balancesheet1.getTotalownersequity().doubleValue() == 0) {
					num=new BigDecimal(0.00);
				}else {
					num=balancesheet.getTotalownersequity().multiply(c)
							.divide(balancesheet1.getTotalownersequity(), 2, RoundingMode.HALF_UP);
				}
				
			}
			table5.openCellRC(15, series).setValue(num.toString());
			table26.openCellRC(6, i+2).setValue(num.toString());
			
			table29.openCellRC(1, i+2).setValue(profit.getYear().toString());
			if(flows.getNetcashflowfromactivities().doubleValue() == 0) {
				table29.openCellRC(2, i+2).setValue("0");
			}else {
				table29.openCellRC(2, i+2).setValue(flows.getNetcashflowfromactivities().toString());
			}
			if(flows.getThecashgeneratedbyaninvestment().doubleValue() == 0) {
				table29.openCellRC(3, i+2).setValue("0");
			}else {
				table29.openCellRC(3, i+2).setValue(flows.getThecashgeneratedbyaninvestment().toString());
			}
			if(flows.getCashforfinancingactivities().doubleValue() == 0) {
				table29.openCellRC(4, i+2).setValue("0");
			}else {
				table29.openCellRC(4, i+2).setValue(flows.getCashforfinancingactivities().toString());
				table29.openCellRC(5, i+2).setValue(flows.getNetincreaseincash().toString());
			}
			if(flows.getNetincreaseincash().doubleValue() == 0) {
				table29.openCellRC(4, i+2).setValue("0");
			}else {
				table29.openCellRC(5, i+2).setValue(flows.getNetincreaseincash().toString());
			}
			
			
			if(2==i){
				devel3=0;
				dataRegion95.setValue(num.toString());
				if(num.doubleValue() > economicGain.getChedging().doubleValue()) {
					dataRegion96.setValue("高于行业平均值，反映公司资本保值、增值情况较好。");
					count.append("公司资本保值增长率高于行业平均值，反映公司资本保值、增值情况较好。");
				}else if(num.doubleValue() >= economicGain.getChedging().doubleValue()-2) {
					dataRegion96.setValue("基本持平于行业平均值，反映公司资本保值、增值情况较一般。");
					count.append("公司资本保值增长率基本持平于行业平均值，反映公司资本保值、增值情况较一般。");
					devel3=1;
				}else {
					dataRegion96.setValue("低于行业平均值，反映公司资本保值、增值情况不佳。");	
					count.append("公司资本保值增长率低于行业平均值，反映公司资本保值、增值情况不佳。");
					devel3=2;
				}
				
				dataRegion97.setValue(count.toString());
				dataRegion159.setValue(count.toString());
				if(devel1 == 0 && devel2 == 0 ||
				   devel1 == 0 && devel3 == 0 ||
				   devel2 == 0 && devel3 == 0
						) {
					dataRegion98.setValue("较强");
				}else if(devel1 == 0 && devel2 == 2 && devel3 != 2  ||
					     devel1 == 0 && devel3 == 2 && devel2 != 2  ||
						 devel2 == 0 && devel3 == 2 && devel1 != 2  ||
						 devel2 == 0 && devel1 == 2 && devel3 != 2  ||
						 devel3 == 0 && devel1 == 2 && devel2 != 2  ||
						 devel3 == 0 && devel2 == 2 && devel1 != 2  
						) {
					dataRegion98.setValue("一般");
				}else{
					dataRegion98.setValue("有待观察");
				}
				

			}
			
			if(profit.getOperatingcosts().add(profit.getBusinesstax()).add(profit.getSellingexpenses()).add(profit.getHandlingexpense())
		    		  .add(profit.getFinancialexpense()).add(profit.getAssetsimpairmentloss()).doubleValue() == 0) {
				table31.openCellRC(3, i+2).setValue("0");
			}else {
				num=profit.getTotalprofit().multiply(c)
					      .divide(profit.getOperatingcosts().add(profit.getBusinesstax()).add(profit.getSellingexpenses()).add(profit.getHandlingexpense())
					    		  .add(profit.getFinancialexpense()).add(profit.getAssetsimpairmentloss()), 2, RoundingMode.HALF_UP);
					table31.openCellRC(3, i+2).setValue(num.toString());
			}
			
			
			
			
			
		}
		
		
		DataRegion dataRegion11 = doc.openDataRegion("PO_SHAREHOLDER");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table6 = dataRegion11.openTable(1);
		List<shareholder> shareholderList=shareholderMapper.selectShareholder(id);
		for(int i = 1;i<shareholderList.size()+1;i++) {
			shareholder shareholder=shareholderList.get(i-1);
			//插入一个空行
			table6.insertRowAfter(table.openCellRC(i, 4));
			table6.openCellRC(i+1, 1).setValue(String.valueOf(i));
			table6.openCellRC(i+1, 2).setValue(shareholder.getNaturalperson());
			table6.openCellRC(i+1, 3).setValue(shareholder.getInvestmentamount().toString());
			table6.openCellRC(i+1, 4).setValue(shareholder.getInvestmentproportion());
		}
		
		DataRegion dataRegion12 = doc.openDataRegion("PO_HISTORIC");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table7 = dataRegion12.openTable(1);
		
		List<historic> historicList=historicMapper.selectHistoric(id); 
		for(int i = 1;i<historicList.size()+1;i++) {
			historic historic=historicList.get(i-1);
			
			table7.insertRowAfter(table.openCellRC(i, 4));
			if(historic.getType() == 0) {
				table7.openCellRC(i+1, 1).setValue("名称变革");
			}else if(historic.getType() == 1) {
				table7.openCellRC(i+1, 1).setValue("法人变更");
			}else if(historic.getType() == 2) {
				table7.openCellRC(i+1, 1).setValue("注册资本变更");
			}else if(historic.getType() == 3) {
				table7.openCellRC(i+1, 1).setValue("注册地址变更");
			}else {
				table7.openCellRC(i+1, 1).setValue("股东变更");
			}
			table7.openCellRC(i+1, 2).setValue(historic.getAlterationin());
			table7.openCellRC(i+1, 3).setValue(historic.getAlterationon());
			table7.openCellRC(i+1, 4).setValue(historic.getTime());
		}
		
		
		DataRegion dataRegion13 = doc.openDataRegion("PO_BRANCH");
		//打开table，openTable(index)方法中的index代表Word文档中table位置的索引，从1开始
		Table table8 = dataRegion13.openTable(1);
		List<branch> branchList = branchMapper.selectBranch(id);
		
		for(int i = 1;i<branchList.size()+1;i++) {
			branch branch = branchList.get(i-1);
			
			table8.insertRowAfter(table.openCellRC(i, 4));
			table8.openCellRC(i+1, 1).setValue(branch.getRelation());
			table8.openCellRC(i+1, 2).setValue(branch.getName());
			table8.openCellRC(i+1, 3).setValue(branch.getAddress());
			table8.openCellRC(i+1, 4).setValue(branch.getPhone().toString());
		}
		
		
		DataRegion dataRegion14 = doc.openDataRegion("PO_FRAMEWORKIMG");
		
		framework framework = frameworkMapper.selectFramework(id);
		dataRegion14.setValue("[image]"+framework.getUrl()+"[/image]");
		//dataRegion14.setValue("[image]http://112.29.173.9:8080/IMG/credit_back/sss.jpg[/image]");
		DataRegion dataRegion52 = doc.openDataRegion("PO_FRAMEWORKIMG1");
		if(framework.getCondition().equals("完善")) {
			dataRegion52.setValue("公司治理结构完善，章程的各项规定符合公司法的各项要求，并根据公司自身的实际情况进行管理，执行情况较好。");
			dataRegion154.setValue("公司治理结构完善，章程的各项规定符合公司法的各项要求，并根据公司自身的实际情况进行管理，执行情况较好。");
		}else if(framework.getCondition().equals("合理")) {
			dataRegion52.setValue("公司治理结构相对合理，能够保证公司正常运营，并根据公司自身的实际情况进行管理，执行情况良好。");
			dataRegion154.setValue("公司治理结构相对合理，能够保证公司正常运营，并根据公司自身的实际情况进行管理，执行情况良好。");
		}else{
			dataRegion52.setValue("公司治理结构有待改善，公司运营效率需要进一步提升。。");
			dataRegion154.setValue("公司治理结构有待改善，公司运营效率需要进一步提升。。");
		}
		
		DataRegion dataRegion15 = doc.openDataRegion("PO_MANAGEMENTSYSTEM");
		Table table9 = dataRegion15.openTable(1);
		
		managementSystem managementSystem = managementSystemMapper.selectManagementSystem(id);
		if(managementSystem.getFinance() == 1) {
			table9.openCellRC(2, 2).setValue("否");
			bool=false;
		}else {
			table9.openCellRC(2, 2).setValue("是");
		}
		
		if(managementSystem.getManpowerresource() == 1) {
			table9.openCellRC(3, 2).setValue("否");
			bool=false;
		}else {
			table9.openCellRC(3, 2).setValue("是");
		}
		
		if(managementSystem.getQuality() == 1) {
			table9.openCellRC(4, 2).setValue("否");
			bool=false;
		}else {
			table9.openCellRC(4, 2).setValue("是");
		}
		
		if(managementSystem.getSafety() == 1) {
			table9.openCellRC(5, 2).setValue("否");
			bool=false;
		}else {
			table9.openCellRC(5, 2).setValue("是");
		}
		
		if(managementSystem.getCredit() == 1) {
			table9.openCellRC(6, 2).setValue("否");
			bool=false;
		}else {
			table9.openCellRC(6, 2).setValue("是");
		}
		
		if(managementSystem.getElses() == null) {
			table9.openCellRC(7, 2).setValue("否");
			bool=false;
		}else {
			table9.openCellRC(7, 2).setValue(managementSystem.getElses());
		}
		
		DataRegion dataRegion53 = doc.openDataRegion("PO_MANAGEMENTSYSTEM1");
		
		if(bool || managementSystem.getElses() != null) {
			dataRegion53.setValue("公司管理制度的建设程度较好,覆盖率高，实施过程中公司能够按照已制定的管理制度运作,整体管理制度建设和执行情况较好。");
			dataRegion155.setValue("公司管理制度的建设程度较好,覆盖率高，实施过程中公司能够按照已制定的管理制度运作,整体管理制度建设和执行情况较好。");
		}
		if(bool == false || managementSystem.getElses() == null) {
			dataRegion53.setValue("公司管理制度相对合理，但仍需改进完善。");
			dataRegion155.setValue("公司管理制度相对合理，但仍需改进完善。");
		}
		
		DataRegion dataRegion16 = doc.openDataRegion("PO_SENIOREXECUTIVE");
		Table table10 = dataRegion16.openTable(1);
		
		List<seniorExecutive> seniorExecutiveList=seniorExecutiveMapper.selectSeniorExecutive(id);
		
		for(int i = 1;i<seniorExecutiveList.size()+1;i++) {
			seniorExecutive seniorExecutive=seniorExecutiveList.get(i-1);
			
			table10.insertRowAfter(table.openCellRC(i, 6));
			table10.openCellRC(i+1, 1).setValue(seniorExecutive.getName());
			table10.openCellRC(i+1, 2).setValue(seniorExecutive.getPosition());
			table10.openCellRC(i+1, 3).setValue(seniorExecutive.getAge().toString());
			table10.openCellRC(i+1, 4).setValue(seniorExecutive.getEducationbackground());
			table10.openCellRC(i+1, 5).setValue(seniorExecutive.getAptitude());
			table10.openCellRC(i+1, 6).setValue(seniorExecutive.getState()+seniorExecutive.getProvince()+seniorExecutive.getCity().toString());
		}
		
		

		DataRegion dataRegion17 = doc.openDataRegion("PO_JOB");
		Table table11 = dataRegion17.openTable(1);
		job job=jobMapper.selectJob(id);
		
		table11.openCellRC(1, 3).setValue(job.getHeadcount().toString());
		table11.openCellRC(3, 3).setValue(job.getGraduatestudent().toString());
		table11.openCellRC(3, 4).setValue(job.getRegular().toString());
		table11.openCellRC(3, 5).setValue(job.getJunior().toString());
		table11.openCellRC(5, 3).setValue(job.getAdvanced().toString());
		table11.openCellRC(5, 4).setValue(job.getIntermediate().toString());
		table11.openCellRC(5, 5).setValue(job.getPrimary().toString());
		table11.openCellRC(7, 3).setValue(job.getFive().toString());
		table11.openCellRC(7, 4).setValue(job.getTowFive().toString());
		table11.openCellRC(7, 5).setValue(job.getTow().toString());
		table11.openCellRC(8, 2).setValue(job.getRegistered().toString());
		
		DataRegion dataRegion58 = doc.openDataRegion("PO_JOB1");
		DataRegion dataRegion59 = doc.openDataRegion("PO_JOB2");
		DataRegion dataRegion60 = doc.openDataRegion("PO_JOB3");
		DataRegion dataRegion61 = doc.openDataRegion("PO_JOB4");
		DataRegion dataRegion62 = doc.openDataRegion("PO_JOB5");
		DataRegion dataRegion63 = doc.openDataRegion("PO_JOB6");
		DataRegion dataRegion64 = doc.openDataRegion("PO_JOB7");
		DataRegion dataRegion65 = doc.openDataRegion("PO_JOB8");
		DataRegion dataRegion66 = doc.openDataRegion("PO_JOB9");
		DataRegion dataRegion67 = doc.openDataRegion("PO_JOB10");
		DataRegion dataRegion68 = doc.openDataRegion("PO_JOB11");
		DataRegion dataRegion69 = doc.openDataRegion("PO_JOB12");
		DataRegion dataRegion70 = doc.openDataRegion("PO_JOB13");
		DataRegion dataRegion71 = doc.openDataRegion("PO_JOB14");
		DataRegion dataRegion72 = doc.openDataRegion("PO_JOB15");
		DataRegion dataRegion73 = doc.openDataRegion("PO_JOB16");
		
		StringBuffer verdict = new StringBuffer();
		
		int headcount = job.getHeadcount();          //员工总数
		dataRegion58.setValue(String.valueOf(headcount));
		dataRegion59.setValue(String.valueOf(job.getRegular()+job.getGraduatestudent()));
		dataRegion60.setValue(String.valueOf((job.getRegular()+job.getGraduatestudent())*100/headcount));     //本科及以上
		if((job.getRegular()+job.getGraduatestudent())*100/headcount > 40) {
			verdict.append("公司从业人员学历情况较高.");
		}else{
			verdict.append("公司从业人员学历相对合理.");
		}
		dataRegion61.setValue(job.getJunior().toString());
		dataRegion62.setValue(String.valueOf((job.getJunior())*100/headcount));     //专科
		dataRegion63.setValue(String.valueOf(job.getAdvanced()+job.getIntermediate()));
		dataRegion64.setValue(String.valueOf((job.getAdvanced()+job.getIntermediate())*100/headcount));     //中级及以上
		if((job.getAdvanced()+job.getIntermediate())*100/headcount > 50) {
			verdict.append("具有中级及以上职称人员占比较高.");
		}else {
			verdict.append("具有中级及以上职称人员占比一般.");
		}
		dataRegion65.setValue(job.getPrimary().toString());
		dataRegion66.setValue(String.valueOf(job.getPrimary()*100/headcount));     //初级
		dataRegion67.setValue(job.getFive().toString());
		dataRegion68.setValue(String.valueOf(job.getFive()*100/headcount));     //五年以上
		dataRegion69.setValue(job.getTowFive().toString());
		dataRegion70.setValue(String.valueOf(job.getTowFive()*100/headcount));     //二到五年
		if((job.getFive()+job.getTowFive())*100/headcount >= 60) {
			verdict.append("从业年限在两年以上的员工数量较多，人员流动性较为稳定，公司从业人员综合素质较高，人才培养制度落实较好");
		}else if((job.getFive()+job.getTowFive())*100/headcount >= 40){
			verdict.append("从业年限在两年以上的员工数量一般，人员流动性较为一般，公司从业人员综合素质良好，人才培养制度落实尚可");
		}else {
			verdict.append("从业年限在两年以上的员工数量较少，人员流动性较为较大，公司从业人员综合素质尚可，人才培养制度落实应加强人才培养");
		}
		dataRegion71.setValue(job.getTow().toString());
		dataRegion72.setValue(String.valueOf(job.getTow()*100/headcount));     //二年以下
		
		dataRegion73.setValue(verdict.toString());
		dataRegion156.setValue(verdict.toString());
		
		
		//债务情况
		DataRegion dataRegion18 = doc.openDataRegion("PO_DEBT");
		Table table12 = dataRegion18.openTable(1);
		balanceSheet balancesheet = balanceSheetList.get(2); 
		BigDecimal debt=balancesheet.getMonetarycapital().add(balancesheet.getReceivables()).add(balancesheet.getNotesreceivable()).add(balancesheet.getPrepayment()).add(balancesheet.getInventory()) 
                .subtract(balancesheet.getShortTermborrowing()).subtract(balancesheet.getAccountspayable()).subtract(balancesheet.getUnearnedrevenue());
		
		table12.openCellRC(1, 2).setValue(balancesheet.getYear().toString());
		table12.openCellRC(2, 2).setValue(debt.toString());
		table12.openCellRC(3, 2).setValue(balancesheet.getTotalownersequity().toString());
		double debt1 = 0;
		if(balancesheet.getTotalownersequity().doubleValue() != 0) {
			debt1 = debt.divide(balancesheet.getTotalownersequity(), 2, RoundingMode.HALF_UP).doubleValue();
		}
		
		table12.openCellRC(4, 2).setValue(String.valueOf(debt1));
		
		DataRegion dataRegion74 = doc.openDataRegion("PO_DEBT1");
		DataRegion dataRegion75 = doc.openDataRegion("PO_DEBT2");
		DataRegion dataRegion76 = doc.openDataRegion("PO_DEBT3");
		 
		dataRegion74.setValue(String.valueOf(debt1));
		if(debt1 > 1) {
			dataRegion75.setValue("高于同行业平均水平，表明公司正常运营情况下的最大债务承受能力控制较好。整体来看，公司目前的债务上限对机构运作有较好的促进作用。");
			dataRegion76.setValue("公司债务情况控制能力较强，流动负债规模较为合理，能够较好的保障投资人权益。");
		}else if(debt1 >= 0.8){
			dataRegion75.setValue("与同行业平均水平基本持平，表明公司正常运营情况下的最大债务承受能力控制尚可，目标公司的债务管理水平良好。");
			dataRegion76.setValue("公司债务情况控制能力良好，流动负债规模属于合理区间，投资人权益基本得到保障。");
		}else {
			dataRegion75.setValue("反映公司正常运营情况下的最大债务承受能力控制不够理想。债务上限占所有者权益的比重较低。整体来看，公司目前的债务上限对机构运作有一定影响。");
			dataRegion76.setValue("公司债务情况控制能力有待提升，应降低流动负债规模，提高债务上限余额，加强对投资人权益的保护能力。");
		}
		
		DataRegion dataRegion19 = doc.openDataRegion("PO_BRAND");
		Table table13 = dataRegion19.openTable(1);
		List<brand> brandList=brandMapper.selectBrand(id);
		
		for(int i = 1;i<brandList.size()+1;i++) {
			brand brand=brandList.get(i-1);
			table13.insertRowAfter(table.openCellRC(i, 3));
			table13.openCellRC(i+1, 1).setValue(brand.getNumber());
			table13.openCellRC(i+1, 2).setValue("[image]"+brand.getUrl()+"[/image]");
			//table13.openCellRC(i+1, 2).setValue("[image]http://112.29.173.9:8080/IMG/credit_back/sss.jpg[/image]");
			table13.openCellRC(i+1, 3).setValue(brand.getOrganization());
		}
		
		
		//专利信息
		DataRegion dataRegion20 = doc.openDataRegion("PO_PATENTTYPE");
		Table table14 = dataRegion20.openTable(1);
		patentType patentType=patentTypeMapper.selectPatentType(id);
		if(patentType == null) {
			table14.openCellRC(1, 2).setValue("0");
			table14.openCellRC(3, 2).setValue("0");
			table14.openCellRC(4, 2).setValue("0");
			table14.openCellRC(5, 2).setValue("0");
		}else {
			table14.openCellRC(1, 2).setValue(patentType.getSum().toString());
			table14.openCellRC(3, 2).setValue(patentType.getInvent().toString());
			table14.openCellRC(4, 2).setValue(patentType.getPractical().toString());
			table14.openCellRC(5, 2).setValue(patentType.getDesign().toString());
		}
		
		
		
		int sum = 0;
		DataRegion dataRegion21 = doc.openDataRegion("PO_PATENT");
		Table table15 = dataRegion21.openTable(1);
		List<patent> patentList=patentMapper.selectPatent(id);
		if(patentList.size() >= 9) {
			sum = 9;
		}else {
			sum = patentList.size()+1;
		}
		
		for(int i = 1;i<sum;i++ ) {
			patent patent=patentList.get(i-1);
			table15.insertRowAfter(table.openCellRC(i, 4));
			table15.openCellRC(i+1, 1).setValue(patent.getTime());
			table15.openCellRC(i+1, 2).setValue(patent.getNumber());
			table15.openCellRC(i+1, 3).setValue(patent.getName());
			if(patent.getType() == 0) {
				table15.openCellRC(i+1, 4).setValue("发明专利");
			}else if(patent.getType() == 1) {
				table15.openCellRC(i+1, 4).setValue("外观设计");
			}else{
				table15.openCellRC(i+1, 4).setValue("实用新型专利");
			}
			
		}
		
		
		DataRegion dataRegion77 = doc.openDataRegion("PO_PATENTTYPE1");
		if(patentList.isEmpty()) {
			dataRegion77.setValue("该公司未申请任何专利技术，需要加大技术研发投入，提高自主研发能力。");
			dataRegion157.setValue("该公司未申请任何专利技术，需要加大技术研发投入，提高自主研发能力。");
		}else if(patentList.size() <= 9) {
			dataRegion77.setValue("该公司的专利技术获得率尚可，研发能力良好。");
			dataRegion157.setValue("该公司的专利技术获得率尚可，研发能力良好。");
		}else {
			dataRegion77.setValue("目标公司共计"+patentType.getSum()+"个注册专利，此处未逐一列举，我司仅提供10个仅供参考，特此说明。");
			dataRegion157.setValue("目标公司共计"+patentType.getSum()+"个注册专利，此处未逐一列举，我司仅提供10个仅供参考，特此说明。");
		}
		

		//软件著作权
		DataRegion dataRegion22 = doc.openDataRegion("PO_COPYRIGHT");
		Table table16 = dataRegion22.openTable(1);
		List<copyright> copyrightList=copyrightMapper.selectCopyright(id);
		
		for(int i = 1;i<copyrightList.size()+1;i++ ) {
			copyright copyright=copyrightList.get(i-1);
			table16.insertRowAfter(table.openCellRC(i, 4));
			table16.openCellRC(i+1, 1).setValue(String.valueOf(i));
			table16.openCellRC(i+1, 2).setValue(copyright.getName());
			table16.openCellRC(i+1, 3).setValue(copyright.getNumber());
			table16.openCellRC(i+1, 4).setValue(copyright.getTime());
		}
		DataRegion dataRegion78 = doc.openDataRegion("PO_COPYRIGHT1");
		
		if(copyrightList.isEmpty()) {
			dataRegion78.setValue("该公司无任何知识产权，无市场竞争优势，公司需要加大技术研发投入和知识产权保护意识。");
		}else if(copyrightList.size() <= 2) {
			dataRegion78.setValue("该公司的知识产权获得率尚可，研发能力和市场竞争优势良好。");
		}else {
			dataRegion78.setValue("该公司知识产权获得率较高，表明公司自主研发能力较强，市场竞争优势较为明显。");
		}
		
		
		
		//行政监管信用记录
		DataRegion dataRegion23 = doc.openDataRegion("PO_ADMINISTRATIVE");
		Table table17 = dataRegion23.openTable(1);
		List<administrative> administrativeList=administrativeMapper.selectAdministrative(id);
		
		if(administrativeList.size() >= 5) {
			sum = 5;
		}else {
			sum = administrativeList.size()+1;
		}
		
		for(int i = 1;i<sum;i++ ) {
			administrative administrative=administrativeList.get(i-1);
			table17.insertRowAfter(table.openCellRC(i, 4));
			table17.openCellRC(i+1, 1).setValue(administrative.getType());
			table17.openCellRC(i+1, 2).setValue(administrative.getPunish());
			table17.openCellRC(i+1, 3).setValue(administrative.getPunisher());
			table17.openCellRC(i+1, 4).setValue(administrative.getTime());
		}
		DataRegion dataRegion79 = doc.openDataRegion("PO_ADMINISTRATIVE1");
		if(administrativeList.isEmpty()) {
			dataRegion79.setValue("经调查，截止信用报告日，该公司在全国企业信用信息公示系统中无重大处罚信息。");
			dataRegion146.setValue("经调查，截止信用报告日，该公司在全国企业信用信息公示系统中无重大处罚信息。");
		}else if(administrativeList.size() <= 4) {
			dataRegion79.setValue("经调查，截止信用报告日，该公司在全国企业信用信息公示系统中共计有"+administrativeList.size()+"条重大处罚信息");
			dataRegion146.setValue("经调查，截止信用报告日，该公司在全国企业信用信息公示系统中共计有"+administrativeList.size()+"条重大处罚信息");
		}else {
			dataRegion79.setValue("经调查，截止信用报告日，该公司在全国企业信用信息公示系统中共计有"+administrativeList.size()+"条重大处罚信息，此处列举5条处罚信息仅供参考，特此说明。");
			dataRegion146.setValue("经调查，截止信用报告日，该公司在全国企业信用信息公示系统中共计有"+administrativeList.size()+"条重大处罚信息");
		}
		
		
		
		//司法监管信用记录
		DataRegion dataRegion24 = doc.openDataRegion("PO_JUDICIAL");
		Table table18 = dataRegion24.openTable(1);
		List<judicial> judicialList=judicialMapper.selectJudicial(id);
		
		if(judicialList.size() >= 5) {
			sum = 5;
		}else {
			sum = judicialList.size()+1;
		}
		
		for(int i = 1;i<sum;i++ ) {
			judicial judicial=judicialList.get(i-1);
			table18.insertRowAfter(table.openCellRC(i, 4));
			table18.openCellRC(i+1, 1).setValue(judicial.getType());
			table18.openCellRC(i+1, 2).setValue(judicial.getBehavior());
			table18.openCellRC(i+1, 3).setValue(judicial.getOffice());
			table18.openCellRC(i+1, 4).setValue(judicial.getTime());
		}
		DataRegion dataRegion80 = doc.openDataRegion("PO_JUDICIAL1");
		if(judicialList.isEmpty()) {
			dataRegion80.setValue("经调查，截止信用报告日，该公司在全国法院系统中无案件执行信息。");
			dataRegion147.setValue("经调查，截止信用报告日，该公司在全国法院系统中无案件执行信息。");
		}else if(judicialList.size() <= 4) {
			dataRegion80.setValue("经调查，截止信用报告日，该公司在全国法院系统中共计有"+judicialList.size()+"条案件执行信息。");
			dataRegion147.setValue("经调查，截止信用报告日，该公司在全国法院系统中共计有"+judicialList.size()+"条案件执行信息。");
		}else {
			dataRegion80.setValue("经调查，截止信用报告日，该公司在全国法院系统中共计有"+judicialList.size()+"条重大处罚信息，此处列举5条案件信息仅供参考，特此说明。");
			dataRegion147.setValue("经调查，截止信用报告日，该公司在全国法院系统中共计有"+judicialList.size()+"条重大处罚信息，此处列举5条案件信息仅供参考，特此说明。");
		}
		
		
		
		//招标投标活动中的违法及处罚
		DataRegion dataRegion25 = doc.openDataRegion("PO_VIOLATIONOPERATION");
		Table table19 = dataRegion25.openTable(1);
		List<violationOperation> violationOperationList=violationOperationMapper.selectViolationOperation(id);
		
		if(violationOperationList.size() >= 5) {
			sum = 5;
		}else {
			sum = violationOperationList.size()+1;
		}
		for(int i = 1;i<sum;i++ ) {
			violationOperation violationOperation=violationOperationList.get(i-1);
			table19.insertRowAfter(table.openCellRC(i, 4));
			table19.openCellRC(i+1, 1).setValue(violationOperation.getBehavior());
			table19.openCellRC(i+1, 2).setValue(violationOperation.getPunish());
			table19.openCellRC(i+1, 3).setValue(violationOperation.getOffice());
			table19.openCellRC(i+1, 4).setValue(violationOperation.getTime());
		}
		
		DataRegion dataRegion152 = doc.openDataRegion("PO_VIOLATIONOPERATION1");
		if(violationOperationList.isEmpty()) {
			dataRegion152.setValue("经调查，该公司无招投标行政处罚和失信记录。");
			dataRegion148.setValue("经调查，该公司无招投标行政处罚和失信记录。");
		}else if(violationOperationList.size() <= 4){
			dataRegion152.setValue("经调查，截止信用报告日，该公司在招投标领域中中共计有"+violationOperationList.size()+"条行政处罚和失信信息。");
			dataRegion148.setValue("经调查，截止信用报告日，该公司在招投标领域中中共计有"+violationOperationList.size()+"条行政处罚和失信信息。");
		}else {
			dataRegion152.setValue("经调查，截止信用报告日，该公司在招投标领域中共计有"+violationOperationList.size()+"条失信处罚信息，此处列举5条失信处罚信息仅供参考，特此说明。");
			dataRegion148.setValue("经调查，截止信用报告日，该公司在招投标领域中共计有"+violationOperationList.size()+"条失信处罚信息，此处列举5条失信处罚信息仅供参考，特此说明。");
		}
		
		//银行信贷履约情况
		creditLoan creditLoan=creditLoanMapper.selectCreditLoan(id);
		DataRegion dataRegion54 = doc.openDataRegion("PO_YEAR");
		balanceSheet balancesheet1 = balanceSheetList.get(2); 
		dataRegion54.setValue(balancesheet1.getYear().toString());
		
		DataRegion dataRegion55 = doc.openDataRegion("PO_DUAN");
		dataRegion55.setValue(balancesheet1.getShortTermborrowing().toString());
		DataRegion dataRegion56 = doc.openDataRegion("PO_CHANG");
		dataRegion56.setValue(balancesheet1.getLongTermloan().toString());
		if(creditLoan != null) {
			bool = true;
			DataRegion dataRegion26 = doc.openDataRegion("PO_INDEBT");
			if(creditLoan.getIndebt() == 0) {
				dataRegion26.setValue("无");
			}else {
				dataRegion26.setValue("有");
				bool = false;
			}
			DataRegion dataRegion27 = doc.openDataRegion("PO_BADNESS");
			if(creditLoan.getBadness() == 0) {
				dataRegion27.setValue("无");
			}else {
				dataRegion27.setValue("有");
				bool = false;
			}
		}
		DataRegion dataRegion81 = doc.openDataRegion("PO_INDEBT1");
		if(bool) {
			dataRegion81.setValue("根据中国人民银行出具的企业基本信用信息报告显示，公司无不良信贷信息记录。");
			dataRegion149.setValue("根据中国人民银行出具的企业基本信用信息报告显示，公司无不良信贷信息记录。");
		}else {
			dataRegion81.setValue("根据中国人民银行出具的企业基本信用信息报告显示，公司有不良信贷信息记录。");
			dataRegion149.setValue("根据中国人民银行出具的企业基本信用信息报告显示，公司有不良信贷信息记录。");
		}
		
		//质量管理
		DataRegion dataRegion28 = doc.openDataRegion("PO_QUALITYCONTROL");
		Table table20 = dataRegion28.openTable(1);
		bool = true;
		List<qualityControl> qualityControlList=qualityControlMapper.selectQualityControl(id);
		
		for(int i = 1;i<qualityControlList.size()+1;i++ ) {
			qualityControl qualityControl=qualityControlList.get(i-1);
			table20.insertRowAfter(table.openCellRC(i, 4));
			table20.openCellRC(i+1, 1).setValue(qualityControl.getName());
			table20.openCellRC(i+1, 2).setValue(qualityControl.getNumber());
			table20.openCellRC(i+1, 3).setValue(qualityControl.getScope());
			table20.openCellRC(i+1, 4).setValue(qualityControl.getTime());
		}
		DataRegion dataRegion82 = doc.openDataRegion("PO_QUALITYCONTROL1");
		if(qualityControlList.isEmpty()) {
			dataRegion82.setValue("经调查，截至本次报告调查日期，暂未通过质量管理体系认证 ");
			dataRegion158.setValue("经调查，截至本次报告调查日期，暂未通过质量管理体系认证");
		}else {
			dataRegion82.setValue("经调查，公司通过了质量管理体系认证，认证标准符合GB/T19001-2016/ISO9001:2015的标准要求。公司近三年在质量管理方面无不良记录，质量管理情况良好。");
			dataRegion158.setValue("经调查，公司通过了质量管理体系认证，认证标准符合GB/T19001-2016/ISO9001:2015的标准要求。公司近三年在质量管理方面无不良记录，质量管理情况良好。");
			bool = false;
		}
		
		
		//环境管理体系
		DataRegion dataRegion29 = doc.openDataRegion("PO_ENVIRONMENTAL");
		Table table21 = dataRegion29.openTable(1);
		List<environmental> environmentalList=environmentalMapper.selectEnvironmental(id);
		
		for(int i = 1;i<environmentalList.size()+1;i++ ) {
			environmental environmental=environmentalList.get(i-1);
			table21.insertRowAfter(table.openCellRC(i, 4));
			table21.openCellRC(i+1, 1).setValue(environmental.getName());
			table21.openCellRC(i+1, 2).setValue(environmental.getNumber());
			table21.openCellRC(i+1, 3).setValue(environmental.getScope());
			table21.openCellRC(i+1, 4).setValue(environmental.getTime());
		}
		
		DataRegion dataRegion83 = doc.openDataRegion("PO_ENVIRONMENTAL1");
		if(environmentalList.isEmpty()) {
			dataRegion83.setValue("经调查，截至本次报告调查日期，暂未通过环境管理体系认证 ");
			
		}else {
			dataRegion83.setValue("经调查，公司通过了环境管理体系认证，认证标准符合GB/T24001-2016/ISO14001:2015的标准要求。公司近三年在环境管理方面无不良记录，环境管理情况良好。");
			bool = false;
		}
		
		
		//安全管理
		DataRegion dataRegion30 = doc.openDataRegion("PO_OCCUPATIONALHEALTH");
		Table table22 = dataRegion30.openTable(1);
		List<occupationalHealth> occupationalHealthList = occupationalHealthMapper.selectOccupationalHealth(id);
		
		for(int i = 1;i<occupationalHealthList.size()+1;i++ ) {
			occupationalHealth occupationalHealth=occupationalHealthList.get(i-1);
			table22.insertRowAfter(table.openCellRC(i, 4));
			table22.openCellRC(i+1, 1).setValue(occupationalHealth.getName());
			table22.openCellRC(i+1, 2).setValue(occupationalHealth.getNumber());
			table22.openCellRC(i+1, 3).setValue(occupationalHealth.getSpecification());
			table22.openCellRC(i+1, 4).setValue(occupationalHealth.getTime());
		}
		
		DataRegion dataRegion84 = doc.openDataRegion("PO_OCCUPATIONALHEALTH1");
		if(occupationalHealthList.isEmpty()) {
			dataRegion84.setValue("经调查，截至本次报告调查日期，暂未通过职业健康安全管理体系认证。");
			
		}else {
			dataRegion84.setValue("经调查，职业健康安全管理体系认证，认证标准符合GB/T 28001-2011 的标准要求。公司近三年在职业健康安全管理方面无不良记录，职业健康安全管理情况良好。");
			bool = false;
		}
		
		
        //其他认证
		DataRegion dataRegion31 = doc.openDataRegion("PO_OTHERPRODUCTS");
		Table table23 = dataRegion31.openTable(1);
		List<otherProducts> otherProductsList = otherProductsMapper.selectOtherProducts(id);
		
		
		for(int i = 1;i<otherProductsList.size()+1;i++ ) {
			otherProducts otherProducts=otherProductsList.get(i-1);
			table23.insertRowAfter(table.openCellRC(i, 4));
			table23.openCellRC(i+1, 1).setValue(otherProducts.getName());
			table23.openCellRC(i+1, 2).setValue(otherProducts.getNumber());
			table23.openCellRC(i+1, 3).setValue(otherProducts.getAccordingto());
			table23.openCellRC(i+1, 4).setValue(otherProducts.getTime());
			
		}
		
		DataRegion dataRegion85 = doc.openDataRegion("PO_OTHERPRODUCTS1");
		if(otherProductsList.isEmpty()) {
			dataRegion85.setValue("经调查，截至本次报告调查日期，暂未通过其他体系认证。 ");
			
		}else {
			dataRegion85.setValue("经调查，公司通过了其他认证，认证标准符合相关的的标准要求，公司近三年在经营方面无不良记录，经营情况良好。");
			bool = false;
		}
		
		
		DataRegion dataRegion86 = doc.openDataRegion("PO_BOOL");
		if(bool) {
			dataRegion86.setValue("公司暂未通过任何体系认证，公司运营过程中应该规范公司管理，注重产品质量，建立高效的运营机制");
		}else {
			dataRegion86.setValue("公司管理体系完善，执行力好，公司员工已经形成一种重制度、守制度的企业文化。");
		}
		
		DataRegion dataRegion32 = doc.openDataRegion("PO_QUALIFICATION");
		Table table24 = dataRegion32.openTable(1);
		
        List<qualification> qualificationList = qualificationMapper.selectQualification(id);
        
        for(int i = 1;i<qualificationList.size()+1;i++ ) {
        	qualification qualification=qualificationList.get(i-1);
			table24.insertRowAfter(table.openCellRC(i, 5));
			table24.openCellRC(i+1, 1).setValue(qualification.getName());
			table24.openCellRC(i+1, 2).setValue(qualification.getLevel());
			table24.openCellRC(i+1, 3).setValue(qualification.getNumber());
			table24.openCellRC(i+1, 4).setValue(qualification.getOffice());
			table24.openCellRC(i+1, 5).setValue(qualification.getTime());
			
		}
		
        
        
        
        DataRegion dataRegion33 = doc.openDataRegion("PO_BUSINESS");
		Table table25 = dataRegion33.openTable(1);
		 DataRegion dataRegion34 = doc.openDataRegion("PO_TOTALMONEY");
		 DataRegion dataRegion37 = doc.openDataRegion("PO_SUM");
		 DataRegion dataRegion36 = doc.openDataRegion("PO_BUSINESS1");
		 Table table27 = dataRegion36.openTable(1);
		 
		 int sum1=0;
		 BigDecimal totalMoney=new BigDecimal(0.00);
		List<professionalWork> professionalWorkList = professionalWorkMapper.selectProfessionalWork(id);
		DecimalFormat    df   = new DecimalFormat("0.00");   
		for(int i = 0;i<professionalWorkList.size();i++) {
			professionalWork professionalWork=professionalWorkList.get(i);
			
			table25.openCellRC(1, i+2).setValue(professionalWork.getYear().toString());
			table25.openCellRC(2, i+2).setValue(String.valueOf(professionalWork.getBusinesssum()));
			table25.openCellRC(3, i+2).setValue(professionalWork.getContractsum().toString());
			
			
			table27.openCellRC(i+2, 1).setValue(professionalWork.getYear().toString());
			if(professionalWork.getDbusiness() < professionalWork.getFbusiness()) {
				table27.openCellRC(i+2, 2).setValue("省外");
	            if(professionalWork.getBusinesssum() == 0) {
	            	table27.openCellRC(i+2, 3).setValue("0.00");
	            }else {
	            	table27.openCellRC(i+2, 3).setValue(df.format((professionalWork.getFbusiness()*100)/professionalWork.getBusinesssum()));
	            }
			}else {
				table27.openCellRC(i+2, 2).setValue("省内");
	            if(professionalWork.getBusinesssum() == 0) {
	            	table27.openCellRC(i+2, 3).setValue("0.00");
	            }else {
	    			table27.openCellRC(i+2, 3).setValue(df.format((professionalWork.getDbusiness()*100)/professionalWork.getBusinesssum()));
	            }
			}
			
			
			sum1=sum1+professionalWork.getBusinesssum();
			totalMoney=totalMoney.add(professionalWork.getContractsum());
		}
		
		dataRegion34.setValue(totalMoney.toString());
		dataRegion37.setValue(String.valueOf(sum1));
        	
		
		
		analysisChart analysisChart = analysisChartMapper.selectAnalysisChart(id);
		DataRegion dataRegion38 = doc.openDataRegion("PO_FIOWDEBT");
		DataRegion dataRegion169 = doc.openDataRegion("PO_FIOWDEBT1");
		dataRegion38.setValue("[image]"+analysisChart.getFiowdebt()+"[/image]");
		//dataRegion38.setValue("[image]http://112.29.173.9:8080/IMG/credit_back/sss.jpg[/image]");
		dataRegion169.setValue(analysisChart.getNonfiowdebt());
		DataRegion dataRegion39 = doc.openDataRegion("PO_FIOWASSET");
		DataRegion dataRegion170 = doc.openDataRegion("PO_FIOWASSET1");
		dataRegion39.setValue("[image]"+analysisChart.getFiowasset()+"[/image]");
		dataRegion170.setValue(analysisChart.getNonfiowasset());
		//dataRegion39.setValue("[image]http://112.29.173.9:8080/IMG/credit_back/sss.jpg[/image]");
		//应收账款
		DataRegion dataRegion175 = doc.openDataRegion("PO_RECEIVABLES");
		DataRegion dataRegion176 = doc.openDataRegion("PO_RECEIVABLES1");
		DataRegion dataRegion177 = doc.openDataRegion("PO_RECEIVABLES2");
		DataRegion dataRegion178 = doc.openDataRegion("PO_RECEIVABLES3");
		DataRegion dataRegion179 = doc.openDataRegion("PO_RECEIVABLES4");
		dataRegion175.setValue("[image]"+analysisChart.getReceivables()+"[/image]");
		int year = balancesheet1.getYear()-2;
		dataRegion176.setValue(year+"——"+balancesheet1.getYear());
		dataRegion177.setValue(balancesheet1.getYear().toString());
		num=balancesheet1.getReceivables().multiply(c)
				.divide(balancesheet1.getTotalassets(), 2, RoundingMode.HALF_UP);
		dataRegion178.setValue(num.toString());
		if(num.doubleValue() > 55) {
			dataRegion179.setValue("低于同行业平均水平，表明该公司的应收账款变现速度较慢，应收账款的管理水平有待提高。");
		}else if(num.doubleValue() >45) {
			dataRegion179.setValue("持平于同行业平均水平，表明该公司的应收账款变现速度一般，应收账款的管理水平尚可。");
		}else {
			dataRegion179.setValue("优于同行业平均水平，表明该公司的应收账款变现速度较快，应收账款的管理水平较高。");
		}
		
		//社会贡献状况SOCIALCONTRIBUTION  
		DataRegion dataRegion44 = doc.openDataRegion("PO_SOCIALCONTRIBUTION");
		Table table32 = dataRegion44.openTable(1);
		socialContribution socialContribution = socialContributionMapper.selectSocialContribution(id);
		table32.openCellRC(2, 2).setValue(socialContribution.getStaff().toString());
		table32.openCellRC(3, 2).setValue(socialContribution.getTollage().toString());
		table32.openCellRC(4, 2).setValue(socialContribution.getNetmargin().toString());
		table32.openCellRC(5, 2).setValue(socialContribution.getContribution().toString());
		table32.openCellRC(6, 2).setValue(socialContribution.getAverageassets().toString());
		table32.openCellRC(7, 2).setValue(socialContribution.getRatecontribution().toString());
		
		
		DataRegion dataRegion165 = doc.openDataRegion("PO_SOCIALCONTRIBUTION1");
		DataRegion dataRegion166 = doc.openDataRegion("PO_SOCIALCONTRIBUTION2");
		DataRegion dataRegion167 = doc.openDataRegion("PO_SOCIALCONTRIBUTION3");
		DataRegion dataRegion168 = doc.openDataRegion("PO_SOCIALCONTRIBUTION4");
		
		dataRegion165.setValue(socialContribution.getContribution().toString());
		dataRegion166.setValue(socialContribution.getAverageassets().toString());
		dataRegion167.setValue(socialContribution.getRatecontribution().toString());
		
		if(socialContribution.getRatecontribution().doubleValue() > 40) {
			dataRegion168.setValue("企业运用全部资产为社会创造或支付价值的能力较强。");
		}else if(socialContribution.getRatecontribution().doubleValue() >= 30) {
			dataRegion168.setValue("企业运用全部资产为社会创造或支付价值的能力一般。");
		}else {
			dataRegion168.setValue("企业运用全部资产为社会创造或支付价值的能力较低。");
		}
		
		
		
		//社会公益
		publicWelfare publicWelfare = publicWelfareMapper.selectPublicWelfare(id);
		DataRegion dataRegion45 = doc.openDataRegion("PO_HANDICAPPED");
		StringBuffer count3 = new StringBuffer();
		DataRegion dataRegion47 = doc.openDataRegion("PO_MONEY");
		if(publicWelfare.getMoney().doubleValue() == 0) {
			dataRegion47.setValue("公司近三年无社会慈善与公益捐款");
			count3.append("公司近三年无社会慈善与公益捐款");
		}else {
			dataRegion47.setValue("社会慈善与公益捐款共计"+publicWelfare.getMoney()+"元。");
			count3.append("社会慈善与公益捐款共计"+publicWelfare.getMoney()+"元。");
		}
		if(publicWelfare.getHandicapped() == 0) {
			dataRegion45.setValue("公司暂未聘用退伍军人");
			count3.append("公司暂未聘用退伍军人");
		}else {
			double handicapped =  (double)publicWelfare.getHandicapped()/job.getRegistered()*100;
            System.out.println(df.format(handicapped));
			dataRegion45.setValue("聘用退伍人士"+publicWelfare.getHandicapped()+"名，退伍军人就业率为"+df.format(handicapped)+"%");
			count3.append("聘用退伍人士"+publicWelfare.getHandicapped()+"名，退伍军人就业率为"+df.format(handicapped)+"%");
		}
		DataRegion dataRegion46 = doc.openDataRegion("PO_VETERAN");
		if(publicWelfare.getVeteran() == 0) {
			dataRegion46.setValue("公司暂未聘用残疾人士");
			count3.append("公司暂未聘用残疾人士");
		}else {
			double veteran =  (double)publicWelfare.getVeteran()/job.getRegistered()*100;	
			dataRegion46.setValue("聘用残疾人士"+publicWelfare.getVeteran()+"名，残疾人就业率"+df.format(veteran)+"%");
			count3.append("聘用残疾人士"+publicWelfare.getVeteran()+"名，残疾人就业率"+df.format(veteran)+"%");
		}
		dataRegion150.setValue(count3.toString());
			
		
		
		//企业荣誉
		DataRegion dataRegion48 = doc.openDataRegion("PO_ENTERPRISEHONOR");
		Table table33 = dataRegion48.openTable(1);
		List<enterpriseHonor> enterpriseHonorList = enterpriseHonorMapper.selectEnterpriseHonor(id);
		for(int i = 1;i<enterpriseHonorList.size()+1;i++) {
			enterpriseHonor enterpriseHonor = enterpriseHonorList.get(i-1);
			table33.insertRowAfter(table.openCellRC(i, 4));
			table33.openCellRC(i+1, 1).setValue(String.valueOf(i));
			table33.openCellRC(i+1, 2).setValue(enterpriseHonor.getContent());
			table33.openCellRC(i+1, 3).setValue(enterpriseHonor.getDepartment());
			table33.openCellRC(i+1, 4).setValue(enterpriseHonor.getTime());
		}
		
		DataRegion dataRegion153 = doc.openDataRegion("PO_ENTERPRISEHONOR1");
		
		if(enterpriseHonorList.isEmpty()) {
			dataRegion153.setValue("经调查，截至本次报告日期，目标公司尚未获得任何荣誉资质，公司获奖率低，有待加强公司管理，提高企业的荣誉水平。");
			dataRegion151.setValue("经调查，截至本次报告日期，目标公司尚未获得任何荣誉资质，公司获奖率低，有待加强公司管理，提高企业的荣誉水平。");
		}else {
			dataRegion153.setValue("目标公司获得荣誉资质良好，公司获奖率高，企业的荣誉水平较好。");
			dataRegion151.setValue("目标公司获得荣誉资质良好，公司获奖率高，企业的荣誉水平较好。");
		}
		
		//结论基本分析
		DataRegion dataRegion49 = doc.openDataRegion("PO_ESTABLISHMENTTIME");
		dataRegion49.setValue(enterpriseInformation.getEstablishmenttime().toString());
		DataRegion dataRegion50 = doc.openDataRegion("PO_REGISTEREDCAPITAL");
		dataRegion50.setValue(enterpriseInformation.getRegisteredcapital().toString());
		DataRegion dataRegion51 = doc.openDataRegion("PO_BUSINESSSCOPE");
		dataRegion51.setValue(enterpriseInformation.getBusinessscope().toString());
		

		
		
		
		
		
		poCtrl.setWriter(doc);
		//设置保存页面
		poCtrl.setSaveFilePage("SaveFile");
		//打开 Word 文档
		//poCtrl.webOpen("D:\\test.doc",OpenModeType.docNormalEdit,"许治涵");
		
		poCtrl.webOpen("http://112.29.173.9:8080/IMG/credit_back/test.doc",OpenModeType.docNormalEdit,"许治涵");
		return "word111";
	}
	
	@RequestMapping("SaveFile")
	public String SaveFile() {
		
		return "SaveFile";
	}
	
	@RequestMapping("index")
	public String index() {
		
		return "index";
	}
	
	
	
	
	
}
